Generate random number in range, validate against existing list and if not matched add number in list

[Kabous]

Good day,

I need some help please, and hope you would be able to assist as I do not seem to be able to find any solution yet for my problem.

My problem is as follow;
I have a document where I will input data in diffrent cells.
I have created a button to assign as random number in a column, validating this random number generated against another sheet with a list of numbers.

This is my first problem here - I have managed to get the number generated randomly, and to have this validated against the exting list with numbers. If this number already exist, must a new random number be assigned.
This is working fine up till the point where a new random number is assigned - the problem however are is that this new assigned number is not validated against the list to see if this doesn't exist.

My second problem here - I would like to add this random number generated, and which are not in the list yet, to the list as well (The list is on another sheet, this code were initially entered on the active sheet)

With this the code which I'm using:

[FONT=Courier New]Sub GenerateRandomNumberInRange()
Dim MinNumber
Dim MaxNumber
Dim InsertRange As Range

Set InsertRange = Range("K20")


MinNumber = 1000
MaxNumber = 9999

InsertRange.Value = Int((Rnd * (MaxNumber - MinNumber + 1)) + MinNumber)

With InsertRange

Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
    With Sheets("Sheet1").Range("A:A") 
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            
        If Not Rng Is Nothing Then
MinNumber = 1000
MaxNumber = 9999

InsertRange.Value = Int((Rnd * (MaxNumber - MinNumber + 1)) + MinNumber)
            
        Else
            MsgBox "Nothing found"
        End If
    End With

End If

End With

End Sub[/FONT]

 

[sijpie]

Why are you asking the user to input something? I thought the number to be searched was randomly generated?

 

[sijpie]

OK here goes.

Replace all your code with this:

Option Explicit


Sub GenerateRandomNumberInRange()
    Const MINNUMBER = 1000
    Const MAXNUMBER = 9999
    Dim InsertRange As Range, rNrList As Range
    
    Randomize


    Set InsertRange = Range("K20")
    Set rNrList = Sheets("Sheet1").Columns(1)
    
    With InsertRange
        Do
            .Value = Int((Rnd * (MAXNUMBER - MINNUMBER + 1)) + MINNUMBER)
            ' if number already exists,  loop with new random number
        Loop While CheckRndNr(.Value, rNrList) = True
    
        ' we now have a new random number, add to list
        AddNr2List .Value, rNrList
    End With
    Set InsertRange = Nothing
End Sub


Function CheckRndNr(iRnd As Integer, rChkR As Range) As Boolean
    Dim rFound As Range
    With rChkR
        Set rFound = .Find(What:=iRnd, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            
    End With
    If Not rFound Is Nothing Then
        CheckRndNr = True
    Else
        CheckRndNr = False
    End If
    
    Set rFound = Nothing
End Function


Sub AddNr2List(iRnd As Integer, rListCol As Range)
    Dim rList As Range
    Dim r1stCell As Range
    
    Set rList = rListCol.Rows(rListCol.Row)    ' set to first element
    If rList.Value = vbNullString Then    ' not in the list, but in empty cells above
        Set rList = rList.End(xlDown)
    End If                      ' now rList at start of list
    
    Set r1stCell = rList
    Set rList = rList.Resize(rList.CurrentRegion.Rows.Count + 1)
    rList(rList.Rows.Count) = iRnd


    'now sort the list for ease of checking
    rList.Sort Key1:=r1stCell, Order1:=xlAscending, Header:=xlGuess


    Set rList = Nothing
    Set r1stCell = Nothing


End Sub

OK, so see what the code does. I am using a couple of functions to make the code easier to follow.
And in answer to yiour question how do you recheck the new random number: you do that through a loop.
See if you fully understand the code.

 

[Kabous]

Hi,

Thanks a mil for your assistance wih regars to this.

There is parts of your provided code which I do understand, however, I'm still learning to anderstand this as well.

This code however is doing exactly what I intend to do on my workseet - thanks for your help with this