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:
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]