Search within List and return msgbox

[Alesiuk27]

Hi,

I am dealing with one very simple excel problem which i should be able to solve, but my VBA skills are unused for 18months and rusty! I also have a second related one which is stretching my knowledge!

First one:

I have a very simple data sheet with has around 4000 lines of data but only 3 colums. Each cell is a number only.

I want to set up a simple macro to start with an InputBox (even i can manage that part) where the use inputs a number, and the macro then searches only column A for that number. A positive match i want one Msgbox (Match found), negative match i want a different Msgbox (Match not found).

This should be a simple lookup and msgbox but i cant get the combination of functions right! Any help?

Second one:

Same data sheet, i want an inputbox which asks the user for 2 bits of data (enter data 1, enter data 2). I then want a macro to search for a match for data 1 in column B, and data 2 in Column C. only when BOTH match do i want a Msgbox with "match found" - anything else i want "match not found".

This one i am struggling with! Any help much appreciated!

 

[Rick Rothstein]

I suppose not, after re-reading, but that was the impression I got after my first read. Maybe I shouldn't be answering questions today. :P

As Peter pointed out, I did not assume the numbers had to be on the same row; however, if that was the requirement, I probably would have posted this...

' #2a (assumes the numbers have to be on the same row)
Sub FindNumbersInColumnBandC()
  Dim FirstNumber As Variant, SecondNumber As Variant
  FirstNumber = Application.InputBox("Enter the Column B number please...", Type:=1)
  SecondNumber = Application.InputBox("Enter the Column C number please...", Type:=1)
  On Error Resume Next
  If Columns("B").Find(FirstNumber, LookAt:=xlWhole).Row <> Columns("C").Find(SecondNumber, LookAt:=xlWhole).Row Then
    MsgBox "Match Not Found"
  Else
    MsgBox "Match Found"
  End If
End Sub

 

[Alesiuk27]

Thanks to all - and yes should have said that for problem 2 i need them on the same row.

Similar to a set up where column B would be "user id" and column C would be "password" (although thats not the use).

 

[Alesiuk27]

As Peter pointed out, I did not assume the numbers had to be on the same row; however, if that was the requirement, I probably would have posted this...

' #2a (assumes the numbers have to be on the same row)
Sub FindNumbersInColumnBandC()
  Dim FirstNumber As Variant, SecondNumber As Variant
  FirstNumber = Application.InputBox("Enter the Column B number please...", Type:=1)
  SecondNumber = Application.InputBox("Enter the Column C number please...", Type:=1)
  On Error Resume Next
  If Columns("B").Find(FirstNumber, LookAt:=xlWhole).Row <> Columns("C").Find(SecondNumber, LookAt:=xlWhole).Row Then
    MsgBox "Match Not Found"
  Else
    MsgBox "Match Found"
  End If
End Sub

Thought i had it using this, but actually its always giving me match not found, even if i do type in a matching set of numbers?

 

[VoG]

Perhaps

Sub ctest()
Dim n, o
n = Application.InputBox("Enter number column B", Type:=1)
o = Application.InputBox("Enter number column c", Type:=1)
If IsNumeric(Application.Match(n, Sheets("Sheet2").Columns("B"), 0)) And IsNumeric(Application.Match(o, Sheets("Sheet2").Columns("C"), 0)) _
    And Application.Match(n, Sheets("Sheet2").Columns("B"), 0) = Application.Match(o, Sheets("Sheet2").Columns("C"), 0) Then
    MsgBox "Match found"
Else
    MsgBox "Match not found"
End If
End Sub

 

[Rick Rothstein]

Thought i had it using this, but actually its always giving me match not found, even if i do type in a matching set of numbers?

Are the numbers in your columns "pure"; that is, are they real numbers and not text because of extra spaces in the entries? Select the cell for one of the numbers that is not working and then click after the number in the Formula Bar... is the text cursor right next to the last digit or does it stand away from that last digit? Just so you know, I tested the code I posted before I posted it and it worked fine on my dummied-up data sample here.

 

[Jythier]

Either will only check the first row it sees a match for the column B number, though...

Edit:

Code!

Sub DoubleMatch()
Dim n As String, o As String
Dim strFirst As String
n = Application.InputBox("Enter number column B", Type:=1)
o = Application.InputBox("Enter number column c", Type:=1)
Dim sh As Worksheet
Set sh = Sheets("Sheet2")
Set cFind = sh.Columns(2).Cells.Find(n)
Do
    If cFind Is Nothing Then Exit Do
    If cFind.Offset(0, 1) = o Then
        MsgBox "Match Found"
        Exit Sub
    Else
        If strFirst = cFind.Address Then Exit Do
        If strFirst = "" Then strFirst = cFind.Address
    End If
    Set cFind = sh.Columns(2).Cells.Find(n, After:=cFind)
Loop
MsgBox "Match not found"
End Sub

 

[Alesiuk27]

Are the numbers in your columns "pure"; that is, are they real numbers and not text because of extra spaces in the entries? Select the cell for one of the numbers that is not working and then click after the number in the Formula Bar... is the text cursor right next to the last digit or does it stand away from that last digit? Just so you know, I tested the code I posted before I posted it and it worked fine on my dummied-up data sample here.

Yup definately pure, i am just using mock data for the moment also so just stuff i entered myself as numbers.

The way it is set up is that the match in column C might not be in the same line as Column B (infact most likely wont be).

Jythier's method seems to work on initial testing - About to test it some more.

Thanks to everyone, been really helpfull and my knowledge is slowly starting to return with every line of VBA i read!

I have some more little tweaks to add to this, the main one being that i want to manipulate the inputbox to ensure that the entry for the first problem can only be a specific number of digits (10) or else an error message is generated.

I think i can do this with Len but do i need seperate arguments for greater than 10 and less than 10, or can i do it in one argument (eg <10>)

I also need to do this on the second problem, complication being that the first entry on it needs to be 4 digits, the second 10 digits........but i figure if i do the first one i can do the second one!

 

[Alesiuk27]

I have managed to get this working with Len checking for the string lenth, using If, but i cant get it to exit if the length isnt met? It just kicks into the next part of the code? (eg the search part).

 

[Jythier]

Try something like,

if len(n) > 10 Then
Msgbox "Number is too long!"
Exit Sub
End if

I assume you can do a messagebox.

 

[Alesiuk27]

Lol yes i can, i had solved it, i had the exit sub and end if the wrong way round........