Populating a select group of cells with randomized names...

[Kevin Lyons]

Hello,

I need some help changing my code below that populates a 10x10 table with
random names (including Null values) to equal 100 names from a range of
names in Column O to a select group of cells that equals 64. For example,
if there were 4 names then each name should appear in those cells 16
times. Conversely, if there were 5 names, each name should appear exactly
12 times with 4 Nulls appearing randomly as well.

Here is the subroutine that correctly populates the 10x10 table:

Sub popTable()
Columns("O").Select
borRow = Selection.Find(What:="Name", After:=activeCell,
LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).row
eorRow = Selection.Find(What:="Null", After:=activeCell,
LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=True).row
Range("O" & borRow + 1 & ":O" & eorRow - 1).Sort Key1:=Range("O" &
borRow), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Set vNames = Range("O" & borRow + 1 & ":O" & eorRow -
1).SpecialCells(xlCellTypeConstants)
numNames = vNames.Count
countNames = Int(100 / numNames)
ReDim vArr(1 To 100)
For i = 0 To numNames - 1
For j = 1 To countNames
vArr(i * countNames + j) = vNames(i + 1)
Next j
Next i
For i = 100 To 2 Step -1
randNum = Int(Rnd() * 100) + 1
tempValue = vArr(i)
vArr(i) = vArr(randNum)
vArr(randNum) = tempValue
Next i
ReDim vResult(1 To 10, 1 To 10)
For i = 1 To 10
For j = 1 To 10
vResult(i, j) = vArr((i - 1) * 10 + j)
Next j
Next i
Range("D4:M13").Value = vResult
For i = 4 To 13
For j = 4 To 13
If Cells(i, j).Value = "" Then
Cells(i, j).Value = "Null"
End If
Next j
Next i
End Sub

Instead of filling the 10x10 array:

Range("D4:M13").Value = vResult

I need to populate the following 64 cells using VBA with the same logic,
however, I am having troubles getting this to work:

Range("B3,B4,B6,B7,B9,B10,B12,B13,B15,B16,B18,B19,B21,B22,B24,B25,B27,B28,B30,B31,B33,B34,B36,B37,B39,"
& _
"B40,B42,B43,B45,B46,B48,B49,V3,V4,V6,V7,V9,V10,V12,V13,V15,V16,V18,V19,V21,V22,V24,V25,V27,V28,V30,V31,"
& _
"V33,V34,V36,V37,V39,V40,V42,V43,V45,V46,V48,V49").Value = vResult

I appreciate any help in getting this written correctly.

Thanks much,

Kevin

 

[Tom Ogilvy]

Sub popTable()
Columns("O").Select
borRow = Selection.Find(What:="Name", _
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Row
eorRow = Selection.Find(What:="Null", _
After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True).Row
Range("O" & borRow + 1 & ":O" & eorRow - 1).Sort _
Key1:=Range("O" & borRow), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Set vNames = Range("O" & borRow + 1 & _
":O" & eorRow - 1).SpecialCells(xlCellTypeConstants)
numNames = vNames.Count
countNames = Int(100 / numNames)
ReDim vArr(1 To 100)
For i = 0 To numNames - 1
For j = 1 To countNames
vArr(i * countNames + j) = vNames(i + 1)
Next j
Next i
For i = 100 To 2 Step -1
randNum = Int(Rnd() * 100) + 1
tempValue = vArr(i)
vArr(i) = vArr(randNum)
vArr(randNum) = tempValue
Next i
ReDim vResult(1 To 100)
k = 1
For i = 1 To 10
For j = 1 To 10
vResult(k) = vArr((i - 1) * 10 + j)
k = k + 1
If vResult(k) = "" Then _
vResult(k) = "Null"
Next j
Next i
Set rng = Range("B3,B4,B6,B7,B9,B10,B12," & _
"B13,B15,B16,B18,B19,B21,B22,B24,B25,B27," & _
"B28,B30,B31,B33,B34,B36,B37,B39," & _
"B40,B42,B43,B45,B46,B48,B49,V3,V4,V6," & _
"V7,V9,V10,V12,V13,V15,V16,V18,V19,V21," & _
"V22,V24,V25,V27,V28,V30,V31," & _
"V33,V34,V36,V37,V39,V40,V42," & _
"V43,V45,V46,V48,V49")
k = 1
For Each cell In rng
cell.Value = vResult(k)
Next

End Sub

might be one approach