code find txt2 in txt1

[erik.van.geit]

Hi, gurus!

What's the starting position of txt2 found in txt1 ?

I can make code that loops through the text
Sub find_txt_position()
txt1 = "abcdEFGhijkEFGlm"
txt2 = "eFG"

For ch = 1 To Len(txt1) - Len(txt2)
If UCase(txt2) = UCase(Mid(txt1, ch, Len(txt2))) Then
Exit For
End If
Next ch
MsgBox "the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch
End Sub

but what's the "right VBA-tool" to do this ?
it is like the worksheetequivalent in the example below

kind regards,
Erik

leads 050121 1328.XLS
	D	E	F	G
1	5	EFG	abcdEFGhijkEFGlm
Blad1

 

[Oaktree]

Why not use application.worksheetfunction.find ?

 

[just_jon]

Howdy, Erik -- see the InStr function.

 

[erik.van.geit]

Thank you,
Oaktree and just_jon,

I was trying FIND but didn't find the right syntax and thought that a "real VBA-solution" existed
Here the results

Sub find_txt_position()
txt1 = "abcdEFGhijkEFGlm"
txt2 = "eFG"

On Error Resume Next
ch = Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))
On Error GoTo 0
If ch <> 0 Then MsgBox "USING : Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch

On Error Resume Next
ch = InStr(1, UCase(txt1), UCase(txt2))
If ch <> 0 Then MsgBox "USING : InStr(1, UCase(txt1), UCase(txt2))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch
On Error GoTo 0

End Sub

IMPORTANT REMARQUE
the worksheetfunction is much slower
I tested the code with 111111 loops
the Instrfunction was almost 7 times faster
Let's make a plea for the VBA-functions.
kind regards,
Erik

 

[erik.van.geit]

here the proof

Sub find_txt_position()
txt1 = "abcdEFGhijkEFGlm"
txt2 = "efG"
sss = 111111

On Error Resume Next
Start = Timer
For x = 1 To sss
ch = Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))
Next
On Error GoTo 0
If ch <> 0 Then MsgBox "USING : Application.WorksheetFunction.Find(UCase(txt2), UCase(txt1))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch & Chr(10) & Chr(10) & _
"USED TIME (" & sss & "loops): " & Timer - Start

On Error Resume Next
Start = Timer
For x = 1 To sss
ch = InStr(1, UCase(txt1), UCase(txt2))
Next
On Error GoTo 0
If ch <> 0 Then MsgBox "USING : InStr(1, UCase(txt1), UCase(txt2))" & Chr(10) & Chr(10) & _
"the text """ & txt2 & """ is found in """ & txt1 & """ starting from character # " & ch & Chr(10) & Chr(10) & _
"USED TIME (" & sss & "loops): " & Timer - Start

End Sub