Compile Error

[billandrew]

I am having a problem resolving a compile error with this line. I find many of the same issues however unable to resolve.

Any suggestions what this issue is.

Thanks guys

range("F5").FormulaR1C1 = "=TRIM(RIGHT(C5,LEN(C10)-SEARCH(" ",C5))" & " "&LEFT(C5,FIND(" ",C5))""

 

[Fluff]

Try

Range("F5").Formula = "=TRIM(RIGHT(C5,LEN(C10)-SEARCH("" "",C5))" & " " & "LEFT(C5,FIND("" "",C5))"

 

[RoryA]

I suspect you want:

Range("F5").Formula = "=TRIM(RIGHT(C5,LEN(C5)-SEARCH("" "",C5))&"" ""&LEFT(C5,FIND("" "",C5)))"

Note: I assumed the C10 reference was meant to be C5 - change it back if not.

 

[billandrew]

Fluff

I've received a run time error applying this script. 1004: Application - defined or object - defined

 

[billandrew]

Yes RoryA the C10 is meant to be C5....

This works perfect... These quotations ugh...

Thank You

 

[billandrew]

If I may gents. Like to include an iferror.

These quotations ugh!

 

[Fluff]

Try

Range("F5").Formula = "=iferror(TRIM(RIGHT(C5,LEN(C5)-SEARCH("" "",C5))&"" ""&LEFT(C5,FIND("" "",C5))),"""")"

 

[billandrew]

Thanks Fluff

Appreciate the help as always...

 

[RoryA]

In case it helps, you can enter the formula you need into a cell, then run this macro, which will output the correct formula string to the immediate window in the VB Editor, so you can then just copy it into the code:

Sub FormulaForVBA()
    Debug.Print Chr(34) & Replace(ActiveCell.FormulaR1C1, Chr(34), Chr(34) & Chr(34)) & Chr(34)
End Sub

 

[billandrew]

That's great to know and have.

Thank You very much