Search for Value from InputBox

[Tempy]

Good day,

I have a value that i get from an input box that i use to filter with,
giving me all the the rows with the criteria.
I would like excel to first check the row to see if the requested value
is there. If it is it must then carry on with the filter, if not i would
like a message box to pop up saying that the criteria requested is not
available.

Could somebody please help with some code on this.

Thanks

Tempy

 

[Bob Phillips]

Something akin to

ans= =Inputbox(.... 'I am guessing this bit, fit to yours

If IsError(Application.Match(ans,Range("A1:A100"), 0)) Then
MsgBox "Not found"
Else
'do yuour thing
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tempy]

Hi Bob,

I treid it but it does not seem to work ? i have enterd both values that
are there and not and i get the message with both of them,despite the
fact that the variable ans still hase the vallue from the input box ?

Tempy

 

[Bob Phillips]

Hi Tempy,

I did a test, albeit simple and cursory, and it worked okay there, so I
suspect something else is going on.

Can you post the code as you have it, and some sample data so that I can
test more accurately?

Bob

 

[Tempy]

Hi Bob,

It is my error as the info it must look up is on another sheet !!

The sheet is called abbreviations, could you guide me as to how i would
look at the other sheet without opening it

Thanks for your help

Tempy

 

[Dave Peterson]

inputbox returns a string.

Are you looking for a string (or a number or a date or what???)

 

[Tempy]

Hello Dave,

It can be just number or alpha or alpha numeric

Tempy

 

[Bob Phillips]

Hi Tempy,

Something like

If IsError(Application.Match(ans,Worksheets("my sheet
name").Range("A1:A100"),0)) Then
MsgBox "Not found"
Else
'do yuour thing
End If

It may be better though to use some extra code to make it more readable,
such as

Set myRange = Worksheets("my sheet name").Range("A1:A100")

If IsError(Application.Match(ans,myRange,0)) Then
MsgBox "Not found"
Else
'do your thing
End If


or even name the range and use that name

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tempy]

Hi Bob,

works like a dream, i am a dabbler at this, thank you so much for your
help.

Tempy

 

[Dave Peterson]

I would have thought that if you were looking for numbers, then you still might
have trouble.