vba string function?

[vbaNewby]

Hello guru's,

I want to know if there is some type of string function that satisfies this example:

myString = "hel"

Cell1: "helloworld"
Cell2: "hel loworld"
Cell3: "hel lo world"

I want to return false for cell 1 but true for cell 2 and 3. Instr function (xlpart param???) will return true for all three. I basically want to return true if "hel" is a substring but its own word within a string.

I hope there is sometype of way to do this without having to write my own function!

Thanks in advance.

 

[vbaNewby]

If you mean is InStr like SEARCH then yes.

As for the 3 conditions, what 3 conditions?

There's only one in that code.

Say this is ActiveCell

Cell1: "helloworld"
Cell2: "hel loworld"
Cell3: "hel lo world"

a = Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(" " & "HEL" & " ", " " & UCase(ActiveCell) & " "))

False for Cell1 but True for Cell2 and Cell3

This is the solution you gave me but for some reason when I set a bunch of variables and run an if statement i.e.:

if a = "true" then
'do something
end if

its not catching within the if statments.

Wondering if:


InStr(1, " " & ActiveCell.Value & " ", " depreciation ", vbTextCompare) > 0




Will give me the same results (0 for cell1 and >0 for Cell2 and Cell3)

I can test it but there is a ton of code to change so I thought you might be able to give me answer....

 

[vbaNewby]

Have a look at this thread, it suggests the same method for what you're trying to do, looks like sound advice to me.

http://www.mrexcel.com/forum/showthread.php?t=579448

That's my post.

Just trying to see if the results are the same for my three cases.

 

[jasonb75]

I noticed it was your thread, I was pointing out that you have been given good advice there.

What seems to have gone un-noticed is that you're using ActiveCell references, I do hope you're not selecting with a loop

 

[vbaNewby]

I noticed, I was pointing out that you have been given good advice there.

In other words, it is the same. Thanks Jason.