#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    2
    Rep Power
    0

    Copying from a list in a column and pasting around random non-empty cells in a range.


    We are creating arrays of DNAs with randomly positioned `blanks' that we are generating in a spreadsheet.

    My question is if there is any way to copy from a list of sample names in a column into a range of cells (column in same spreadsheet) that contains a few randomly filled cells (filled with the word 'blank').





















    Essentially I'd like to skip over those filled cells and just paste the next item in the list into the next empty cell in the destination range.

    Here is the code used to populate the destination range with the `blanks':










































































    If AssayComboBox.Value = "xxx" Then

































































    Range("$B$2:$B$7").

























































































    Value = "Blank"
    Response = MsgBox("Is this a full (96 well/88 Sample) Run?",


    vbYesNo + vbQuestion, "xxx")












































































    Select Case Response
    Case vbYes:










































































    For i = 1 To 2





























































































    Randomize
    Range("$B$10:$B$97").SpecialCells(xlCellTypeBlanks)(Int(Range("$B$10:$B$97").SpecialCells(xlCellType Blanks).Count * Rnd + 1)).Value = "Blank"
    Next














































































































    Apologies for the presumably simple question, but I am a complete newbie.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2017
    Posts
    2
    Rep Power
    0
    Copying from a list in a column and pasting around random non-empty cells in a range:

    We are creating arrays of DNAs with randomly positioned `blanks' that we are generating in a spreadsheet.

    My question is if there is any way to copy from a list of sample names in a column into a range of cells (column in same spreadsheet) that contains a few randomly filled cells (filled with the word 'blank'). Essentially I'd like to skip over those filled cells and just paste the next item in the list into the next empty cell in the destination range.

    Here is the code used to populate the destination range with the `blanks'.

    If AssayComboBox.Value = "xxx" Then
    Range("$B$2:$B$7").Value = "Blank"
    Response = MsgBox("Is this a full (96 well/88 Sample) Run?", vbYesNo + vbQuestion, "xxx")
    Select Case Response
    Case vbYes:
    For i = 1 To 2
    Randomize
    Range("$B$10:$B$97").SpecialCells(xlCellTypeBlanks)(Int(Range("$B$10:$B$97").SpecialCells(xlCellType Blanks).Count * Rnd + 1)).Value = "Blank"
    Next

IMN logo majestic logo threadwatch logo seochat tools logo