Search within List and return msgbox

Alesiuk27

New Member
Joined
Nov 7, 2011
Messages
17
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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
For #1 try

Code:
Sub btest()
Dim n
n = Application.InputBox("Enter number", Type:=1)
If IsNumeric(Application.Match(n, Columns("A"), 0)) Then
    MsgBox "Match found"
Else
    MsgBox "Match not found"
End If
End Sub
 
Upvote 0
Are you against using worksheet cells for the input and other worksheet cells for the response? The first one would just become a match function incased in an 'if' function that would be something like...
=if(iserror(match(a1, 'Othersheet'!A:A, 0)),"Match not found","Match Found")

The second one would have a spreadsheet formula answer as well but I don't know it offhand... some of these more advanced guys will, though.
 
Upvote 0
For #2

Code:
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, Columns("B"), 0)) And IsNumeric(Application.Match(o, Columns("C"), 0)) Then
    MsgBox "Match found"
Else
    MsgBox "Match not found"
End If
End Sub
 
Upvote 0
Thanks very much VoG, certainly looking at the first one thats a far simpler solution than expected, not one ive used before.

The only thing i forgot to add is that my data is on a second sheet (the macro starting buttons on sheet1 - can i just change the search value under Application.Match?
 
Upvote 0
Try like this

Code:
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)) Then
    MsgBox "Match found"
Else
    MsgBox "Match not found"
End If
End Sub
 
Upvote 0
Here is another method to consider...

Code:
' #1
Sub FindNumberInColumnA()
  Dim NumberIn As Variant
  NumberIn = Application.InputBox("Enter a number please...", Type:=1)
  If Columns("A").Find(NumberIn, LookAt:=xlWhole) Is Nothing Then
    MsgBox "Match Not Found"
  Else
    MsgBox "Match Found"
  End If
End Sub
 
' #2
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)
  If Columns("B").Find(FirstNumber, LookAt:=xlWhole) Is Nothing Or Columns("C").Find(SecondNumber, LookAt:=xlWhole) Is Nothing Then
    MsgBox "Match Not Found"
  Else
    MsgBox "Match Found"
  End If
End Sub
 
Last edited:
Upvote 0
Here is another method to consider...

Code:
' #1
Sub FindNumberInColumnA()
  Dim NumberIn As Variant
  NumberIn = Application.InputBox("Enter a number please...", Type:=1)
  If Columns("A").Find(NumberIn, LookAt:=xlWhole) Is Nothing Then
    MsgBox "Match Not Found"
  Else
    MsgBox "Match Found"
  End If
End Sub
 
' #2
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)
  If Columns("B").Find(FirstNumber, LookAt:=xlWhole) Is Nothing Or Columns("C").Find(SecondNumber, LookAt:=xlWhole) Is Nothing Then
    MsgBox "Match Not Found"
  Else
    MsgBox "Match Found"
  End If
End Sub
Doesn't this not take into account whether the two values are in the same row?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top