?
Solved

DoAlert message when value already exists in  an defined range...

Posted on 2003-03-21
5
Medium Priority
?
197 Views
Last Modified: 2013-12-25
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
Comment
Question by:Marin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
Cimperiali earned 80 total points
ID: 8181860
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
 
LVL 1

Author Comment

by:Marin
ID: 8206449
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
 
LVL 5

Expert Comment

by:Cimperiali
ID: 8208835
;-)
Have a nice day and happy coding,

Cesare Imperiali
0
 
LVL 1

Author Comment

by:Marin
ID: 10375003
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
 
LVL 5

Expert Comment

by:Cimperiali
ID: 10379947
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question