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

Kabous

New Member
Joined
Mar 29, 2013
Messages
5
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:
Code:
[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]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
Why are you asking the user to input something? I thought the number to be searched was randomly generated?
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
OK here goes.

Replace all your code with this:
Code:
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

New Member
Joined
Mar 29, 2013
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,532
Messages
5,659,368
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top