Random Number Generator VBA

[VBA learner ITG]

Hi all,

I need your advice in what i am doing wrong.

I can generate random numbers using the below code but i cannot add a prefix of "HTC" to the numbers generated using the below code.

Sub GenerateRandoms()
    ' Define the minimum, maximum of the range and how many random
    ' numbers are needed
    Const Min As Long = 100000
    Const Max As Long = 999999
    Const HowMany As Long = 10
    ' Define the column where randoms are wanted and starting row as well
    Const StartRow As Long = 2
    Const Col As String = "a"
    Dim LastRow As Long
    Dim Ws As Worksheet
    Dim i As Long, j As Long, Temp As Long, Number As Long
    Dim Arr
    ' Error Checking
    If Max = 0 Then
        MsgBox "Maximum number can not be 0"
        Exit Sub
    End If
    If HowMany = 0 Then
        MsgBox "Number of required Randoms can not be 0"
        Exit Sub
    End If
    If Min > Max Then
        MsgBox "Minimum is more than Maximum"
        Exit Sub
    End If
    If Max - Min + 1 < HowMany Then
        MsgBox "Number of Randoms required should not be more than Max - Min + 1"
        Exit Sub
    End If
    ' If your worksheet is not Sheet1, change here appropriately
    Set Ws = Worksheets("Sheet1")
    Application.ScreenUpdating = False
    Number = Max - Min + 1
    ReDim Arr(1 To Number, 1 To 1)
    ' Generate all possible number between Min and Max
    For i = Min To Max
        Arr(i - Min + 1, 1) = i
    Next i
    Randomize
    ' Shuffle the array generated above randomly
    For i = 1 To Number
        j = Int((Number - i + 1) * Rnd) + i
        Temp = Arr(i, 1)
        Arr(i, 1) = Arr(j, 1)
        Arr(j, 1) = Temp
    Next i
    'Copy into the Worksheet those many records which are requested
    Ws.Range(Col & StartRow & ":" & Col & StartRow + HowMany - 1) = Arr
    
    Application.ScreenUpdating = True
End Sub

 

[6StringJazzer]

I think you want to change this

        Arr(i - Min + 1, 1) = i

to this

        Arr(i - Min + 1, 1) = "HTC" & i

 

[Fluff]

You need to change Temp from Long to String

 

[VBA learner ITG]

Thank you for your assistance, It has worked. I spent hours playing around with the code and you helped resolve the issue in minutes of me seeking advice from my peers.

Final Code below for those who may find it useful.

Sub GenerateRandoms()
    ' Define the minimum, maximum of the range and how many random
    ' numbers are needed
    Const Min As Long = 100000
    Const Max As Long = 999999
    Const HowMany As Long = 10
    ' Define the column where randoms are wanted and starting row as well
    Const StartRow As Long = 2
    Const Col As String = "a"
    Dim LastRow As Long
    Dim Ws As Worksheet
    Dim i As Long, j As Long, Temp As String, Number As Long
    Dim Arr
    ' Error Checking
    If Max = 0 Then
        MsgBox "Maximum number can not be 0"
        Exit Sub
    End If
    If HowMany = 0 Then
        MsgBox "Number of required Randoms can not be 0"
        Exit Sub
    End If
    If Min > Max Then
        MsgBox "Minimum is more than Maximum"
        Exit Sub
    End If
    If Max - Min + 1 < HowMany Then
        MsgBox "Number of Randoms required should not be more than Max - Min + 1"
        Exit Sub
    End If
    ' If your worksheet is not Sheet1, change here appropriately
    Set Ws = Worksheets("Sheet1")
    Application.ScreenUpdating = False
    Number = Max - Min + 1
    ReDim Arr(1 To Number, 1 To 1)
    ' Generate all possible number between Min and Max
    For i = Min To Max
    
    '''zack original code
    
        'Arr(i - Min + 1, 1) = i
        Arr(i - Min + 1, 1) = "HTC" & i
    Next i
    Randomize
    ' Shuffle the array generated above randomly
    For i = 1 To Number
        j = Int((Number - i + 1) * Rnd) + i
        Temp = Arr(i, 1)
        Arr(i, 1) = Arr(j, 1)
        Arr(j, 1) = Temp
    Next i
    'Copy into the Worksheet those many records which are requested
    Ws.Range(Col & StartRow & ":" & Col & StartRow + HowMany - 1) = Arr
    
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Glad we could help & thanks for the feedback