Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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
0
Marin
Asked:
Marin
  • 3
  • 2
1 Solution
 
CimperialiCommented:
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 lngCounter As Long
strValue = Worksheets("Sheet1").Cells(1, 1).Value
Application.Cursor = xlWait
For lngCounter = 1 To 5000
   DoEvents
   If strValue = CStr(Worksheets("Sheet2").Cells(lngCounter, 1).Value) Then
      Application.Cursor = xlDefault
      MsgBox "The value in Sheet1, cell row 1 col 1 is the same as the value in Sheet2, cell row " & lngCounter & " col 1"
      Exit Sub
   End If
Next lngCounter

Application.Cursor = xlDefault
MsgBox "Scan completed. No similar value found "
End Sub
0
 
MarinAuthor Commented:
Hello Cimperiali,

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
0
 
CimperialiCommented:
;-)
Have a nice day and happy coding,

Cesare Imperiali
0
 
MarinAuthor Commented:
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
0
 
CimperialiCommented:
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(1, 1).Value
    Application.Cursor = xlWait
    For lngCounter = 1 To 5000
       DoEvents
       If strValue = CStr(Worksheets("Sheet2").Cells(lngCounter, 1).Value) Then
          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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now