Parsing a vba's variables data

[warren_s]

Hi
I have 4 variables with words in them for example

one="dog"
two="cat"
three="bug"
four="bird"

I have a 5th variable with a sentence in it for example

Sen=”The bug and bird are in the park”

What I need to know is which of the variables content appears in the variable Sen first and what position it is. In this case it would be variable three position 5.

Does anyone have an idea how to do this in VBA, I would appreciate your suggestions.
Thank you very much in advance.
Warren

 

[lalbatros]

use the instr function

 

[warren_s]

--------------------------------------------------------------------------------

use the instr function

Can you please give me an exmple, I have no clue what that is.
Thank you.
Warren

 

[lalbatros]

This is a vba function.
I think "search" is an equivalent "worksheet function", but I have never used it since I mostly parse things in vba.

 

[Jonmo1]

Instr Searches for a string within another string. Returns the position number it's found in. Returns 0 if not found. It Works Almost exactly the same as search or find worksheet functions...

Syntax is like this

Instr(Start Postion,String Searched In,String Searched For)

Start position is optional, defaults to 1

so something like this, using your variables..

If Instr(1,Sen,One) > 0 Then

it is case sensitive, to make it NOT case sensitive, use the the UCase Function..

If Instr(1,Ucase(Sen),Ucase(One)) > 0 Then

You would have to construct a loop or something to go through all four of your variables....

Hope this helps..

 

[Richard Schollar]

You can use the 4th argument of Instr to specify what kind of comparison you want to use (eg vbBinaryCompare = case sensitive, vbTextCompare = case insensitive).

 

[Jonmo1]

Ahh, thanks Richard.

for some reason, I only read the help files enough to learn what I needed to use instr. So I never read far enough to learn what the 4th argument was for...Guess that's what I get...LOL.

 

[Jonmo1]

This is the definition of the 4th argument in the Help Files...

<TABLE cellSpacing=4 cols=2 cellPadding=4><TBODY><TR vAlign=top><TD class=T width="16%">compare</TD><TD class=T width="84%">Optional. Specifies the type of string comparison. If compare is Null, an error occurs. If compare is omitted, the Option Compare setting determines the type of comparison. Specify a valid LCID (LocaleID) to use locale-specific rules in the comparison.</TD></TR></TBODY></TABLE>

Not very helpfull if you ask me....

But if you click on the hyperlink for "string comparison" it takes you to this..

string comparison

A comparison of two sequences of characters. Use Option Compare to specify binary or text comparison. In English-U.S., binary comparisons are case sensitive; text comparisons are not.

That probably would have helped....

 

[warren_s]

Line = "asd."
xx = "."

posit = InStr(1, xx, Line)
_________________________________________________
Hi Again,
I must be doing something wrong. I put the above code in and posit keeps coming up zero and it should come up 4.
Can you please tell me what I am doing wrong.
Thank you.
Warren

 

[Norie]

You've got xx and Line the wrong way round.

By the way you don't really need the 1, that's a default.