VBA - Using InStr to find substring and cut characters into offset cell

spidaman

Board Regular
Joined
Jul 26, 2015
Messages
116
Office Version
  1. 365
Platform
  1. Windows
Can anyone help with an error I am getting when using the InStr function please? :confused:

I want the code to identify the column with header 'To' which can occur in variable position, then search the 'To' column for a specific substring. For each cell containing the substring I need the Offset.(0, -1) cell value to equal the substring plus all characters to the right of the substring (this will never be more than 20 characters).

To make it clear titRng is my title range, ToRng is my 'To' column range and MyString1 is the substring I want to find.

I have tried this using .Find function but also struggled with that option so am trying to make it work with InStr method.

Code:
Sub Action_MyCel_v1()


Dim Mycel As Range, foundCell1 As Range, titRng As Range, ToRng As Range
Dim wb1 As Workbook
Dim ws1 As Worksheet, origTAB1 As Worksheet
Dim MyPos1 As Integer
Dim TargetStr1 As String, MyString1 As String


Set wb1 = ActiveWorkbook
Set ws1 = wb1.Sheets("Messages")


    ws1.Copy After:=Sheets(Sheets.Count)
    Set origTAB1 = ActiveSheet
    origTAB1.Name = "Original Messages"
    
    ws1.Activate
    
    TargetStr1 = "To"
    Set titRng = ws1.Rows(1)


    Set foundCell1 = titRng.Find(What:=TargetStr1, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=True, SearchFormat:=False)
    
    Set ToRng = foundCell1.EntireColumn
    
    MyString1 = "Name:"
    
    For Each Mycel In ToRng
    
        ' Error thrown on next row
        If InStr(1, Mycel.Value, MyString1) > 0 Then


            MyPos1 = InStr(Mycel, MyString1, 1)
            Mycel.Offset(0, 1).Value = Mid(MyString1, MyPos1, 20)
        
        End If
        
    Next Mycel


End Sub

I assume I can't use MyString1 within the InStr function but if not then how else can I make it work? Also I'd like to actually cut the characters out of the cell rather than just copy them.

Any suggestions welcome and thanks in advance for taking a look.
 
I think it was too late last night when I was looking at this. I re-read the OP and think maybe this version will work.

Code:
Sub Action_MyCel_v2()
Dim Mycel As Range, foundCell1 As Range, titRng As Range, ToRng As Range
Dim wb1 As Workbook
Dim ws1 As Worksheet, origTAB1 As Worksheet
Dim MyPos1 As Integer
Dim TargetStr1 As String, MyString1 As String
Set wb1 = ActiveWorkbook
Set ws1 = wb1.Sheets("Messages")
    ws1.Copy After:=Sheets(Sheets.Count)
    Set origTAB1 = ActiveSheet
    origTAB1.Name = "Original Messages"
    ws1.Activate
    TargetStr1 = "To"
    Set titRng = ws1.Rows(1)
    Set foundCell1 = titRng.Find(What:=TargetStr1, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=True, SearchFormat:=False)
    Set ToRng = Intersect(foundCell1.EntireColumn, ws1.UsedRange)
    MyString1 = "Name:"
    For Each Mycel In ToRng
        ' Error thrown on next row
        If InStr(Mycel.Value, MyString1) > 0 Then
            MyPos1 = InStr(Mycel, MyString1)
            Mycel.Offset(0, 1).Value = Mid(Mycel.Value, MyPos1, 20)
        End If
    Next Mycel
End Sub

Hi JLGWhiz thanks for your help with this. The above code also does exactly what I need it to so much appreciated.

It looks like specifying the range using the Intersect method here has done the trick. Was pleased that my use of InStr at least seems to work correctly though as I thought that was the problem originally as this was where the error was being thrown.

I am wondering now which method is quicker/more efficient - using InStr or .Find then With (@Rick Rothstein) - because I have several more actions to add in the code depending on the presence of various other substrings?
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi JLGWhiz thanks for your help with this. The above code also does exactly what I need it to so much appreciated.

It looks like specifying the range using the Intersect method here has done the trick. Was pleased that my use of InStr at least seems to work correctly though as I thought that was the problem originally as this was where the error was being thrown.

I am wondering now which method is quicker/more efficient - using InStr or .Find then With (@Rick Rothstein) - because I have several more actions to add in the code depending on the presence of various other substrings?

I am pretty sure Rick's code is much faster. He is an ace at condensed and rapid code. I have yet to master the Evaluate function like he has. As for efficiency, the user will have to judge that based on the results they get when using the code.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top