August 30th, 2017, 02:21 PM
-
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.
August 30th, 2017, 02:24 PM
-
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