Using the Find Function inside the Right function in macro

[namruht]

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!