Syntax of Excel Find Function in VBA

[Ken]

I want to extract data from a cell using the MID function
in VBA.

In cell A1 I have "XXX: TT"

As part of a macro I want to use this code:

MID(Range("A1"),X+1,2) to return the "TT" of cell A1.

The X above is the string location of the position of the
colon. I can't figure out the syntax in VBA to set X equal
to the location of the colon in the string. We use
the "Find" function in Excel to do that but how do you
make this happen in VBA?

TIA.

 

[Dave Peterson]

Take a look at InStr in VBA's help:

But if the cell's value always had that format, how about just using:

with activesheet
myStr = right(.range("a1").value,2)
end with

 

[pfsardella]

See the'Instr' function in VBE help.

dim X as integer
X = Instr(1, Range("A1"), ":", 1)

HTH
Paul

 

[Ken]

Thanks....
And if I wanted the third occurence on the colon in the
string, would I have to nest these functions somehow?

 

[Dave Peterson]

I'd just keep looking:

Option Explicit
Sub testme()

Dim myStr As String
Dim iCtr As Long
Dim colonPos As Long
Dim findWhichOne As Long

findWhichOne = 3

myStr = "::::asdf:asdf:asdf:asdf:"

colonPos = 0
For iCtr = 1 To findWhichOne
colonPos = InStr(colonPos + 1, myStr, ":")
If colonPos = 0 Then
'3rd not found
Exit For
End If
Next iCtr

If colonPos > 1 Then
MsgBox colonPos
Else
MsgBox findWhichOne & " wasn't found"
End If

End Sub