VBA code gives error message Type Mismatch

[Liz_I3]

HI
The below code produces a runtime error I3 Type mismatch

It removes all text in a cell (string) before the last ""

ActiveWorkbook.Worksheets(z).Cells(i, 5).Formula = "=Right(d" & i & ", Len(d" & i & ") - Find(""@"", Substitute(d" & i & ", " \ ", ""@"", (Len(d" & i & ") - Len(Substitute(d" & i & ", " \ ", ""))) / Len(" \ "))))"

The original formula below works great in excel but I wanted to create a macro.

=RIGHT(D2,LEN(D2)-FIND("@",SUBSTITUTE(D2,"","@",(LEN(D2)-LEN(SUBSTITUTE(D2,"",""))/LEN("")))))

my VBA is not great so all help is greatly appreciated

L

 

[RoryA]

You forgot to double up all the quotes:

"=Right(d" & i & ", Len(d" & i & ") - Find(""@"", Substitute(d" & i & ", "" \ "", ""@"", (Len(d" & i & ") - Len(Substitute(d" & i & ", "" \ "", """"))) / Len("" \ ""))))"

 

[Liz_I3]

Thank You Rory very much, it work perfectly

L

 

[RoryA]

Glad to help.