Syntax of Excel Find Function in VBA

  • Thread starter Thread starter Ken
  • Start date Start date
K

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.
 
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
 
See the'Instr' function in VBE help.

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

HTH
Paul
 
Thanks....
And if I wanted the third occurence on the colon in the
string, would I have to nest these functions somehow?
 
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
 
Back
Top