Formula FIND error within VBA

[Eurekaonide]

Dear All

I have the following code which I would like to input the formula =RIGHT($H4,LEN($H4)-FIND(" ",$H4)) This is because I have a series of names and they start with Mr, Miss, Mrs etc and I need to remove this and just have the names. The formula does a wonderful job but I would like to add it into my VBA code when I am manipulating and sorting the data. However when I run it comes back with an error saying the FIND is a sub or function not defined. Any ideas how I can overcome this please?


Sub Test()
LastRow = Range("J" & Rows.Count).End(xlUp).Row 'Sets the last row
Range("L2").Select
Range("L2:L" & LastRow).FormulaR1C1 = Right(RC8, Len(RC8) - Find(" ", RC8))
End Sub

 

[Peter_SSs]

You need to
- enclose your formula in quotes
- include an = at the start of the formula
- double-up any quotes already in the formula

So try

Range("L2:L" & LastRow).FormulaR1C1 = "=Right(RC8, Len(RC8) - Find("" "", RC8))"

 

[Eurekaonide]

Perfect thank you so much I had tried putting the " and extra = but not the extra " in the formula part, thanks so much for your help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.