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!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Jythier

New Member
Joined
Oct 10, 2011
Messages
42
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Alesiuk27

New Member
Joined
Nov 7, 2011
Messages
17

ADVERTISEMENT

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?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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:

Jythier

New Member
Joined
Oct 10, 2011
Messages
42
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?
 

Jythier

New Member
Joined
Oct 10, 2011
Messages
42
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

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
Top