Search for Value from InputBox

T

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

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

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)
 
T

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

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

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
 
T

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

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

Dave Peterson

inputbox returns a string.

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

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)
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top