Using the Find Function inside the Right function in macro

I have a column with data that I am wanting to clean up and place into a new column.

I begin by finding the last row with data and setting that to LR
Then I create a new column and add a value for a header

I want to to use the right function in conjunction with the Find function to look for a specific string and return everything to the right of that string as a value I am placing in a newly created column.

I am getting a Type mismatch error 13.

I have narrowed down, by removing pieces of code, that the issue is with the Find portion of the code in this line.

Below is my stab at performing this task. Any help would be much appreciated. I am new to vba and learning as I go.

Sub cleanUpFileName()

Dim LR As Long, i As Long

LR = Range("C" & Rows.Count).End(xlUp).Row
Worksheets(1).Columns(4).Insert shift:=xlRight
Worksheets(1).Range("D1").Value = "test_file"

For i = 2 To LR
With Range("C" & i)
Select Case .Value
Case "": .Offset(, 1).Value = "Null"
'Case Else: .Offset(, 1).Value = Right(.Value, Len(.Value) - .Find("testfiles/", (.Value)) - 9)
Case Else: .Offset(, 1).Value = Right(.Value, Len(.Value) - .Find(What:="testfiles/", LookIn:=(.Value), MatchCase:=False))
End Select
End With
Next i

End Sub



Thanks in advance!

Please Login or Register  to view this content.

, not LookIn:=(.Value)

If you are trying to find the position of one string within another have a look at InStr.

Please Login or Register  to view this content.

You might also want to look at Mid, Left and Split.

Find is used to find things, eg values, in a range, not within a string.

This would have returned the value in the range where the search term was found.

Please Login or Register  to view this content.

That value would most likely be text, and since the second argument of Right should be a number you would get a type mismatch error.