THE "INSTR" FUNCTION !!!

J

jay dean

Hello -

Both combobox5 and textbox1 contain text strings. I am trying to
determine if the string selected from the dropdown list in combobox5 is
found in textbox1. If I use the following statement:

If InStr(ComboBox5.Value, TextBox1.Value, vbBinaryCompare) > 0 then ....

I get the error: "Type mismatch."
Any help with a better statement would be appreciated.

Thanks!
Jay

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

Jacob Skaria

If InStr(1,TextBox1.Text,ComboBox5.Text,vbTextCompare ) > 0 Then

End If

If this post helps click Yes
 
R

Rick Rothstein

The InStr function has a strange syntax in that it has an optional first and
fourth argument (an optional first argument is very unusual). Normally, one
uses the default values for the optional arguments (specifying only the text
to search and the string to look for). The first argument can be specified
if need be without having to specify the fourth argument; HOWEVER, if you
plan to specify the fourth argument, you MUST specify the first one as well.
So, as Jacob posted, your statement needs to look like this...

If InStr(1, ComboBox5.Value, TextBox1.Value, vbBinaryCompare) > 0 Then.

I would note that, UNLESS you used an Option Compare statement in your code
module to change it, Binary comparisons are the default meaning that you
would not have to explicitly specify vbBinaryCompare to get a Binary
comparison. So, if you don't have an Option Compare statement active, you
should be able to just do this...

If InStr(ComboBox5.Value, TextBox1.Value) > 0 Then
 
J

jay dean

Okay, thanks Jacob and Rick! Is it possible to use the EXCEL functions,
"FIND()" and "SEARCH()" to achieve the same goal when dealing with a
combobox and a textbox?

If so, how will the statement be like? I am just curious !!

Thanks!
Jay

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

Rick Rothstein

It is more efficient to use the built-in VB functions that reaching out to
the worksheet functions (unless the worksheet functions provide a
functionality that is not in VB and can't easily be coded for). Also,
sometimes it can be a pain to properly construct the arguments for the
worksheet functions from within VB. In any event, you can use the worksheet
function by using the WorksheetFunction property of the Application object.
 

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

Similar Threads


Top