CODEWITHSUNDEEP
excelvbarandom
Borit
Borit

Reputation: 1

If cell A1 is certain value then B1 is random between two values

I want to assign a random number between 2 values if a cell is a certain value, using VBA. I have 40 randomly generated numbers between 1 and 12, and then I want to assign to each of those 40 numbers a random number between two values, but these two values are dependent on whether the number is 1,2,3,....,12. My code looks like this now but the numbers that come out are not always between the limits that I give. What is wrong?

Sub measurepoints()


Dim i As Integer
Dim j As Integer


For i = 2 To 41
     Cells(i, 1).Value = "=RANDBETWEEN(1,12)"
Next i

For j = 2 To 41

If Cells(j, 1).Value = 1 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,2)"

ElseIf Cells(j, 1).Value = 2 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,2)"

ElseIf Cells(j, 1).Value = 3 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,8)"

ElseIf Cells(j, 1).Value = 4 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,8)"

ElseIf Cells(j, 1).Value = 5 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,8)"

ElseIf Cells(j, 1).Value = 6 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,8)"

ElseIf Cells(j, 1).Value = 7 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,4)"

ElseIf Cells(j, 1).Value = 8 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,2)"

ElseIf Cells(j, 1).Value = 8 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,8)"

ElseIf Cells(j, 1).Value = 10 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,10)"

ElseIf Cells(j, 1).Value = 11 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,4)"

ElseIf Cells(j, 1).Value = 12 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,8)"



  End If


Next j

End Sub

Upvotes: 0

Views: 243

Answers (2)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

No need for VBA:

=RANDBETWEEN(1,INDEX({2,2,8,8,8,8,4,2,8,10,4,8},A1))

should do what you want

To recalculate all function in a sheet, press F9.

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166136

you have a typo:

ElseIf Cells(j, 1).Value = 8 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,2)"

ElseIf Cells(j, 1).Value = 8 Then
    Cells(j, 2).Value = "=RANDBETWEEN(1,8)"

Upvotes: 1

Related Questions