Macro Help

[banjoflanjo]

Hello All,

I have 2 different macros, "macro1" & "macro2".

I also have a database of unique project numbers with a named range "list1".

Above the database I have an entry box named "findme".

What I need if possible is for the user to be able to enter a project number into the entry box, then for a macro to check the database, if the project is found in the list run "macro1", if not found in the list then run "macro2".

Can anyone help?

Thanks

Banjoflanjo

 

[VoG]

Try

Sub test()
If IsNumeric(Application.Match(Range("Findme"), Range("list1"), 0)) Then
    Call macro1
Else
    Call macro2
End If
End Sub

 

[banjoflanjo]

Hi VoG,

Thanks for your reply,

This does not seem to work. All of the projects listed start with the letter "C" and is followed by a 7 digit number eg. C1234567

Does the "IsNumeric" bit affect this?

Banjoflanjo

 

[VoG]

The IsNumeric is used to check whether a match was found. The code works for me with numeric and non-numeric data, including your example.

The code looks for an exact match. Check for leading or trailing spaces in the findme cell and in list1.

 

[banjoflanjo]

Still doesnt work for some reason.

My database contains many columns and rows, does the code search all of the named range or just the first column like a vlookup?

Banjoflanjo

 

[VoG]

Ah! Try

Sub test()
Dim Found As Range
Set Found = Range("list1").Find(what:=Range("findme").Value, lookat:=xlWhole)
If Not Found Is Nothing Then
    Call macro1
Else
    Call macro2
End If
End Sub

 

[banjoflanjo]

Good man! Thats done it, thank you for your help with this VoG!