Solved: Randomization

[SeanJ]

Thank you guys and girls for helping me on my projects. I need help with randomization.

I have the following code but I need to put a random number between 1 and 12. for each column. There are 10 columns.

This is for my daughter to get her to learn the Multiplication table faster.

Thanks for the help.

[vba]
Sub test()
Dim i As Integer
Dim x As Integer

For x = 1 To 10

Select Case x

Case Is < 2
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 2
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i
Case 3
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 4
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 5
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 6
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 7
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 8
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 9
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

Case 10
For i = 1 To 10
ActiveSheet.Cells(i, x).Value = x & " X " & i & " ="
Next i

End Select

Next x
End Sub
[/vba]

[Bob Phillips]

Not sure where it would go in your code, but you can get such a random number with

[vba]

evaluate("randbetween(1,12)")
[/vba]

BTW, what is the Case statement for, itv seems superfluous.

[SeanJ]

The case statement are for the cloumns so when X = 2 then it start again in column B and etc....

If you got a better way, I am all for it.

I change it a little, but If someone got a better way then please tell me.

[vba]
Sub test()
Dim i As Integer
Dim x As Integer
For x = 1 To 5

Select Case x

Case 1
Call RandomNo(x)

Case 2
Call RandomNo(x)
Case 3
Call RandomNo(x)

Case 4
Call RandomNo(x)

Case 5
Call RandomNo(x)


End Select

Next x
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("D").Select
Selection.Insert Shift:=xlToRight
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Columns("A:I").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
'.WrapText = False
'.Orientation = 0
'.AddIndent = False
'.IndentLevel = 0
'.ShrinkToFit = False
'.ReadingOrder = xlContext
'.MergeCells = False
End With
Range("A1").Select
End Sub
Function RandomNo(x As Integer)
For i = 1 To 20
Randomize
MyNumber = Int((12 - 1 + 1) * Rnd + 1)
MyNumber1 = Int((12 - 1 + 1) * Rnd + 1)

ActiveSheet.Cells(i, x).Value = (MyNumber) & " X " & (MyNumber1) & " ="
Next i
End Function

[/vba]

[mdmackillop]

Hi Sean,
Looks like a worthy cause. A bit of tidying, and a bit of code to save your brain!
Regards
MD
[VBA]
Option Explicit
Sub Test()
Dim i As Long, x As Long
Application.ScreenUpdating = False
With Range("A1:J20")
.ClearContents
.Interior.ColorIndex = xlNone
End With
For x = 1 To 5
For i = 1 To 20
ActiveSheet.Cells(i, x).Value = Int(12 * Rnd + 1) _
& " X " & Int(12 * Rnd + 1) & " ="
Next i
Next x
For x = 2 To 10 Step 2
Columns(x).Insert
Next
With Columns("A:I")
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
End With
Range("A2").Select
Application.ScreenUpdating = True
End Sub

[/VBA]

[Gert Jan]

Hi Malcolm,

Would you please care to explain a part of this to me? As my math skills are even less (if so possible ) then my vba skills,
[vba]Function RandomNo(x As Integer)
Dim i As Long, MyNumber As Long, MyNumber1 As Long
For i = 1 To 20
Randomize
MyNumber = Int((12 - 1 + 1) * Rnd + 1)
MyNumber1 = Int((12 - 1 + 1) * Rnd + 1)
ActiveSheet.Cells(i, x).Value = (MyNumber) & " X " & (MyNumber1) & " ="
Next i
End Function[/vba]

the piece 12-1+1 doesn't make sense to me, when i see that i think it's still 12.
Does vb look at that differently?

Gert Jan

[mdmackillop]

You're right Gert. I didn't look at the function as it seemed to work. In retrospect I've incorporated it into the main sub. Certainly 12 -1 + 1 = 12, so that's gone as well.

Sean,
A Function is used generally to return a value to the calling sub, eg
[VBA]
Sub Test()
msg = Times(2)
MsgBox msg
End Sub

Function Times(x As Long)
Times = 3 * x
End Function

[/VBA]
otherwise, just call a sub to enter values in the worksheet etc.

[Gert Jan]

Thanks, i already had this vision of me, training myselsf a couple of ours a day with your workbook.

Gert Jan

PS nice trick

[SeanJ]

Thanks mdmackillop that works. Thanks everyone for your help.