Hello i have some code that i want to run, if the conditions are not met, remember that 1st time run and if data deleted that the condition would not be met. If the conditions are met i want to the code to run but for the next row.
The codes run for each row i = 5:29 so starting at B5 finishing at B29. The code runs for B5:G5 and then H5 is a calculated cell that sums up the values in (B5:G5) it is this cell (H5:H29) that i want to check against the conditions
The code randomly selects numbers from a named range ("ALL") or might be ("PartNumbers") depending on the pool of numbers required. It then puts them into row B5:G5 H5 is then Calculated if H5 is >=11 and <=170 then next row
If H5 is not >=111 and <=170 then repeat the selection process until condition is met, then advance to next row to perform the same code again all the way down to row 29
Dim NumArr As Object, i As Long, j As Long, rng As Range, cell As Range
Application.ScreenUpdating = False
With Me
Set NumArr = CreateObject("System.Collections.ArrayList")
If .CboxHot.Value = "All" Then ' this selects between 2 named ranges called All (1-58) and PartNumbers (1-24)
Set rng = .Range("All")
ElseIf .CboxHot.Value = ("PartNumbers") Then
Set rng = .Range("PartNumbers")
End If
If (.Shapes("MonOn").Visible = True) Or (.Shapes("WedOn").Visible = True) Or (.Shapes("SatOn").Visible = True) And .CboxHot.Value = "All" And .cboxodd.Value = 1 Then 'cboxodd chooses how many odd numbers to be selected
For i = 5 To 29
NumArr.Clear ' clear the array
j = 1 ' no of odd numbers to select
Do Until j > 1 '
Set cell = RdmCell(rng) ' set cell to the random selection
' Consider only ODD numbers
If cell.Value Mod 2 = 1 Then ' only allow odd numbers
If Not cell.Value = "" And Not NumArr.contains(cell.Value) Then
NumArr.Add cell.Value
j = j + 1 ' move to next selection in the array as this is 1 it will move on
End If
End If
Loop ' loop until
Cells(i, 2).Resize(, 1) = NumArr.toarray
Next i
For i = 5 To 29
NumArr.Clear
j = 1
Do Until j > 5
Set cell = RdmCell(rng)
' Consider only Even 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, 3).Resize(, 5) = NumArr.toarray
Next i
ElseIf (.Shapes("MonOn").Visible = True) Or (.Shapes("WedOn").Visible = True) Or (.Shapes("SatOn").Visible = True) And .CboxHot.Value = "All" And .cboxodd.Value = 2 Then
' Set NumArr = CreateObject("System.Collections.ArrayList")
'Set rng = .Range("All")
For i = 5 To 29
NumArr.Clear
j = 1
Do Until j > 2
Set cell = RdmCell(rng)
' Consider only ODD numbers
If cell.Value Mod 2 = 1 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, 2).Resize(, 2) = NumArr.toarray ' loop until 2 Odd valid selections made
Next i
For i = 5 To 29
NumArr.Clear
j = 1
Do Until j > 4
Set cell = RdmCell(rng)
' Consider only Even 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, 4).Resize(, 4) = NumArr.toarray ' loop until 4 Even valid selections
Next i
'This is the random selection function
Function RdmCell(rng As Range) As Range
Set RdmCell = rng.Cells(Int(Rnd * rng.Cells.Count) + 1)
End Function
Bookmarks