VBA Looping Find text within string help needed

[cb12]

Evening,

I have had a look through previous htreads but seem unable to find an appropriate solution so thought I would create a new thread.

I have c.20,000 rows and in column I there is data along the format of (for example) "Date: October 21st / xxxxxxxxxxxxxxxxxxxxxx" where the xxxxx could be any number of characters.

I want to be able to remove the "Date: October 21st / " without having to create 365 find and replaces.

I'm currently using the procedure:

Sub Month_Remover()
    
    Dim MyString As String
    Dim StringLen, Pos As Integer
    Dim Result As Variant

    MyString = ActiveCell
    StringLen = Len(MyString)
    Pos = StringLen - 20
    Result = Right(MyString, Pos)
    
        ActiveCell.Value = Result
        ActiveCell.Offset(1, 0).Select

End Sub

However I'm having problems cycling it through the data (without it looping constantly through the spreadsheet until it crashes). Does anyone have any ideas on how I might do this more efficiently? Any help will be most welcome.

One other thing the line "Pos = StringLen - 20" would obviously have to changed for each month but I would be happy to have 12 different iterations rather than 365.

Regards

CB

 

[stilesbn]

I would use this formula and find a way to incorporate it into the VBA code.

=LEFT(G15,FIND("/",G15))

 

[cb12]

Thank you - a new perspective always helps.

I have edited the code so it will read something along the lines of

Sub Month_Remover()
Dim MyString As String
Dim StringLen As Integer
Dim Pos As Integer
Dim result As Variant

    MyString = ActiveCell
    StringLen = Len(MyString)
    Pos = StringLen - ActiveCell.Offset(0, 10).Value
    result = Right(MyString, Pos)

    ActiveCell.Value = result
        ActiveCell.Offset(1, 0).Select
    
End Sub

where ActiveCell.Offset(0, 10).Value will equal =FIND("/",I10) or the relevant row.

Probably not ideal but it'll keep me going for now. Thank you.

Edit: I'm sure there's a way to mark the thread as completed but I can't see where.