VBA - Error Randomly Generating Numbers

[mrcyvr]

Hello,
My script gives a randomly generated number between a range of numbers the user specifys. Take 2100 and 2199. The script will generate a random number between those numbers. But before the script generates the number - I want to compare it too a list on another sheet to see if the number already was generated previously.


I have the script working, as in, it generates the number, but I get a 'method 'range' of object_worksheet failed' error. Then VBE highlights the 'myresult = isNumber' line.

Any ideas why that could be?

Private Sub CommandButton1_Click()
Dim i As Long
Dim myResult As Boolean
Dim myValue As Integer

    Low = Application.InputBox("Minimum Number", Type:=1)
    High = Application.InputBox("Maximum Number ", Type:=1)
    Selection.Clear

MsgBox "Sorry this number exists. Excel will now generate a new number!"
    For Each cell In Selection.Cells
        If WorksheetFunction.CountA("A:A") = (High - Low + 1) Then Exit For
        Do
            rndNumber = Int((High - Low + 1) * Rnd() + Low)
        Loop Until Selection.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndNumber
                Next
                    myResult = IsNumeric(Application.Match(myValue, Range("Sheet4!A1:A1631"), 0))
        ActiveCell.Offset(1).Activate

        End Sub

 

[Yongle]

helpful hints
I do not understand the logic of your code - I would expect to see VBA test the value first before message box telling user that "the number already exists"
It is good practice to declare all variables properly - it can help avoid and debugging code issues (low, high, rndNumber, cell are not declared in your procedure)

I have the script working, as in, it generates the number, but I get a 'method 'range' of object_worksheet failed' error. Then VBE highlights the 'myresult = isNumber' line.

Any ideas why that could be?

Code fails if MATCH function does not find a match
- handle it like this

        On Error Resume Next
        myResult = IsNumeric(Application.Match(myValue, Range("Sheet4!A1:A1631"), 0))
        On Error GoTo 0

you may want the code to do something different if a match is not found

        On Error Resume Next
        myResult = IsNumeric(Application.Match(myValue, Range("Sheet4!A1:A1631"), 0))
        If Err.Number > 0 Then
            MsgBox "nothing found"
            [I][COLOR=#ff0000]do something....[/COLOR][/I]
        End If
        On Error GoTo 0

 

[mikerickson]

I think this will do what you want

Sub test()
    Dim Low As Long, High As Long
    Dim arrNumbers() As Long
    Dim Pointer As Long, i As Long, randIndex As Long, temp As Long
    Dim oneCell As Range
    
    Low = Application.InputBox("Low", Default:=1, Type:=1)
    If Low <= 0 Then Exit Sub: Rem cancel
    High = Application.InputBox("High", Default:=3, Type:=1)
    If High < Low Then Exit Sub: Rem cancel
    
    ReDim arrNumbers(Low To High)
    For i = Low To High: arrNumbers(i) = i: Next i
    Pointer = High
    
    For Each oneCell In Selection.Cells
        randIndex = Low + Int((Pointer - Low) * Rnd())
        oneCell.Value = arrNumbers(randIndex)
        
        temp = arrNumbers(randIndex)
        arrNumbers(randIndex) = arrNumbers(Pointer)
        arrNumbers(Pointer) = temp
        
        Pointer = Pointer - 1
        If Pointer = 0 Then Pointer = High
    Next oneCell
End Sub