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

*** Sent via Developersdex http://www.developersdex.com ***

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" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

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

*** Sent via Developersdex http://www.developersdex.com ***

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" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

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

*** Sent via Developersdex http://www.developersdex.com ***

inputbox returns a string.

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

Tempy wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

Hello Dave,

It can be just number or alpha or alpha numeric

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

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" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

Hi Bob,

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

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

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



Tempy wrote:

Hello Dave,

It can be just number or alpha or alpha numeric

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson