I have some code that works fine , but I have to copy and paste the code many times. this can then get confusing when you are changing certain parts of the code.
I was wondering if it could be put in to functions and then you just call the function u want ?
Private Sub CommandButton1_Click()
Dim NumArr As Object, i As Long, j As Long, rng As Range, cell As Range
Application.ScreenUpdating = False
With Sheets("Games")
ElseIf MultiPage10.Value = 4 And Me.cbGames = "All" Then
Set NumArr = CreateObject("System.Collections.ArrayList")
Set rng = .Range("All") ' this range of cells changes
For i = 5 To 28 ' this is number of rows so this can change
NumArr.Clear
j = 1
Do Until j > 3
Set cell = RdmCell(rng)
' Consider only ODD numbers
If cell.Value Mod 2 = 1 Then ' this changes for odd and even
If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
NumArr.Add cell.Value
j = j + 1
End If
End If
Loop
Cells(i, 38).Resize(, 3) = NumArr.toarray ' this changes as is destination of data
Next i
For i = 5 To 28
NumArr.Clear
j = 1
Do Until j > 3
Set cell = RdmCell(rng)
' Consider only ODD numbers
If cell.Value Mod 2 = 0 Then
If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
NumArr.Add cell.Value
j = j + 1
End If
End If
Loop
Cells(i, 41).Resize(, 3) = NumArr.toarray
Next i
End If
End With
Application.ScreenUpdating = True
End Sub
Function RdmCell(rng As Range) As Range
Set RdmCell = rng(Int(Rnd * rng.Count) + 1)
End Function
Bookmarks