Marin
asked on
DoAlert message when value already exists in an defined range...
Hello,
If i have a Value in in Excel Sheet 1 in cell a1.
I want a Macro to look for the Value in Excel Sheet 2 Range a1:a5000.
If the Macro finds that Value I want the Macro to give a Doalert message! And abort the Macro so the user has to change the Value. And then the user has to push the Macro button again.
If the Macro does noet find the Value in the defined Range I want the Macro to find the first empty Cell in the Range and fill up the Cell with that Value.
I think this should be possibble in VB and I tryed a couple of things already but I can't get it work.
So please help me!?!?!
Thanks in advance!
Marin
If i have a Value in in Excel Sheet 1 in cell a1.
I want a Macro to look for the Value in Excel Sheet 2 Range a1:a5000.
If the Macro finds that Value I want the Macro to give a Doalert message! And abort the Macro so the user has to change the Value. And then the user has to push the Macro button again.
If the Macro does noet find the Value in the defined Range I want the Macro to find the first empty Cell in the Range and fill up the Cell with that Value.
I think this should be possibble in VB and I tryed a couple of things already but I can't get it work.
So please help me!?!?!
Thanks in advance!
Marin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
;-)
Have a nice day and happy coding,
Cesare Imperiali
Have a nice day and happy coding,
Cesare Imperiali
ASKER
Hello Cesare!
The code you wrote above is working very well!!
I've used it a couple of times!!
Now i need a simular code, only now it should give the MsgBox when the code can't find the given value...
I tryed it by changeing the = operator in to the <> (Not equal to) operator....
But i can't get it work! :S
I hope you can help me out!
Thanks in advance!
Marin
The code you wrote above is working very well!!
I've used it a couple of times!!
Now i need a simular code, only now it should give the MsgBox when the code can't find the given value...
I tryed it by changeing the = operator in to the <> (Not equal to) operator....
But i can't get it work! :S
I hope you can help me out!
Thanks in advance!
Marin
Here you are:
To tell value has not been found, you search through all thee cells.
If you find it, you set a bolean value to true and exit the cells scan.
Once you're out of the scan, you test for the boolean value:
true= you found it
false= you did not find, thus you can dispaly the message...
'Add a commandbutton to your excel sheet, and add this code:
'Warning: "Sheet1" or "Sheet2" used here must match real name
'of your sheets!
Private Sub CommandButton1_Click()
Dim strValue As String
Dim bFound As Boolean 'false by default
Dim lngCounter As Long
strValue = Worksheets("Sheet1").Cells
Application.Cursor = xlWait
For lngCounter = 1 To 5000
DoEvents
If strValue = CStr(Worksheets("Sheet2").
bFound = True
Exit For
End If
Next lngCounter
Application.Cursor = xlDefault
If Not bFound Then
MsgBox "Scan completed. No similar value found "
End If
End Sub
To tell value has not been found, you search through all thee cells.
If you find it, you set a bolean value to true and exit the cells scan.
Once you're out of the scan, you test for the boolean value:
true= you found it
false= you did not find, thus you can dispaly the message...
'Add a commandbutton to your excel sheet, and add this code:
'Warning: "Sheet1" or "Sheet2" used here must match real name
'of your sheets!
Private Sub CommandButton1_Click()
Dim strValue As String
Dim bFound As Boolean 'false by default
Dim lngCounter As Long
strValue = Worksheets("Sheet1").Cells
Application.Cursor = xlWait
For lngCounter = 1 To 5000
DoEvents
If strValue = CStr(Worksheets("Sheet2").
bFound = True
Exit For
End If
Next lngCounter
Application.Cursor = xlDefault
If Not bFound Then
MsgBox "Scan completed. No similar value found "
End If
End Sub
ASKER
Thnx for the EXCELLENT comment.
In the MsgBox you let it tell where the similar value was found. That's really great!!
Thnx again!
Bye Marin