Hi there,

I am writing some code in Excel VBA, and I need to find the position of
a colon in a string variable. I thought I would be able to use the
'find' function to return the position value as I would on a normal
worksheet. However, VBA doesn't seem to recognise the function ("Sub or
Function not Defined"). I have also checked the help files and there
seems to be no mention of it. Could somebody please advise me on an
alternative.

Many thanks,

Dave


--
SportsDave
------------------------------------------------------------------------
SportsDave's Profile: http://www.excelforum.com/member.php...o&userid=34641
View this thread: http://www.excelforum.com/showthread...hreadid=544078

look in help index for

instr

--
Don Guillett
SalesAid Software

"SportsDave" wrote
in message ...

Hi there,

I am writing some code in Excel VBA, and I need to find the position of
a colon in a string variable. I thought I would be able to use the
'find' function to return the position value as I would on a normal
worksheet. However, VBA doesn't seem to recognise the function ("Sub or
Function not Defined"). I have also checked the help files and there
seems to be no mention of it. Could somebody please advise me on an
alternative.

Many thanks,

Dave


--
SportsDave
------------------------------------------------------------------------
SportsDave's Profile:
http://www.excelforum.com/member.php...o&userid=34641
View this thread: http://www.excelforum.com/showthread...hreadid=544078

Hi SportsDave,

Try something like:

'=============
Public Sub Tester()
Dim sStr As String
Dim pos As Long

sStr = "test;String"

pos = InStr(1, sStr, ";", vbTextCompare)

MsgBox pos
End Sub
'<<=============


---
Regards,
Norman



"SportsDave" wrote
in message ...

Hi there,

I am writing some code in Excel VBA, and I need to find the position of
a colon in a string variable. I thought I would be able to use the
'find' function to return the position value as I would on a normal
worksheet. However, VBA doesn't seem to recognise the function ("Sub or
Function not Defined"). I have also checked the help files and there
seems to be no mention of it. Could somebody please advise me on an
alternative.

Many thanks,

Dave


--
SportsDave
------------------------------------------------------------------------
SportsDave's Profile:
http://www.excelforum.com/member.php...o&userid=34641
View this thread: http://www.excelforum.com/showthread...hreadid=544078

Hi SportsDave,

pos = InStr(1, sStr, ";", vbTextCompare)


For your purposes, repalce the shown semicolon string with a colon!


---
Regards,
Norman

if the string is typed in A1 then something like the below will do it:
hopethis helps
J

Sub FindColon()

Dim SearchString, SearchChar, MyPos

SearchString = Cells(1, 1) 'range("A1") contains the string
SearchChar = ":" 'Search for ":".

MyPos = InStr(1, SearchString, SearchChar, vbTextCompare)

MsgBox "The colon was character: " & MyPos

End Sub

Dim s as String, iloc as Long
s = "Some string with a : contained within"
iloc = Instr(1,s,":",vbTextCompare)

--
Regards,
Tom Ogilvy


"SportsDave" wrote
in message ...

Hi there,

I am writing some code in Excel VBA, and I need to find the position of
a colon in a string variable. I thought I would be able to use the
'find' function to return the position value as I would on a normal
worksheet. However, VBA doesn't seem to recognise the function ("Sub or
Function not Defined"). I have also checked the help files and there
seems to be no mention of it. Could somebody please advise me on an
alternative.

Many thanks,

Dave


--
SportsDave
------------------------------------------------------------------------
SportsDave's Profile:
http://www.excelforum.com/member.php...o&userid=34641
View this thread: http://www.excelforum.com/showthread...hreadid=544078

Thanks a lot everyone. Help very much appreciated.

Dav

--
SportsDav
-----------------------------------------------------------------------
SportsDave's Profile: http://www.excelforum.com/member.php...fo&userid=3464
View this thread: http://www.excelforum.com/showthread.php?threadid=54407