VBA - Return position of text string from within a cell
Posted by JAF on June 07, 2001 5:04 AM
Hiya
I need to genreate a variable which will give me the starting position of a text string (in this case "MC") within a larger text string in a cell.
Example: Cell A1 contains the following text:
MR A & MRS B MCNULTY
Using Excel's FIND function, returns the (correct) value of 14 - the text string of "MC" begins at the 14th character in cell A1.
I've tried to replicate this worksheet function in VBA, but can't get it to work.
I'm sure this is one of those instances of me missing the blindingly obvious, but I'm really stuck on this one!!
Any help gratefully received!
Posted by Tuc on June 07, 2001 5:15 AM
You want to use the instr function.
This function returns the position where the search string is found.
sub Test()
dim intPos as integer
dim strTest1 as string
dim strTest2 as string
strTest1 = "MR A & MRS B MCNULTY"
strTest2 = "MC"
intpos = instr(1, strTest1, strTest2, vbTextCompare)
msgbox intpos
end sub
This will return 14, or the position where the second string was found. It started at position 1. If nothing is found then it returns a 0.
I also use it in IF statements a lot.
IF instr(1, strTest1, strTest2, vbTextCompare)<> 0 THEN ...
Tuc
Posted by JAF on June 07, 2001 6:03 AM
Thanks for that - I was almost there!!
Posted by Chuck on June 07, 2001 8:26 PM
You might try the following:
VTarget = "mc"
myVar = Application.WorksheetFunction.Find(VTarget, Range("a1"))
