code find txt2 in txt1

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
DEFG
15EFGabcdEFGhijkEFGlm
Blad1
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
Why not use application.worksheetfunction.find ?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Thank you,
Oaktree and just_jon,

I was trying FIND but didn't find :p the right syntax and thought that a "real VBA-solution" existed
Here the results
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
here the proof
Code:
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
 

Forum statistics

Threads
1,147,688
Messages
5,742,627
Members
423,744
Latest member
bkirtland

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top