Compile Error

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
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

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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Try
Code:
Range("F5").Formula = "=TRIM(RIGHT(C5,LEN(C10)-SEARCH("" "",C5))" & " " & "LEFT(C5,FIND("" "",C5))"
 
Upvote 0
I suspect you want:

Code:
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.
 
Upvote 0
Fluff

I've received a run time error applying this script. 1004: Application - defined or object - defined
 
Upvote 0
Yes RoryA the C10 is meant to be C5....

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

Thank You
 
Last edited:
Upvote 0
Try
Code:
Range("F5").Formula = "=iferror(TRIM(RIGHT(C5,LEN(C5)-SEARCH("" "",C5))&"" ""&LEFT(C5,FIND("" "",C5))),"""")"
 
Upvote 0
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:

Code:
Sub FormulaForVBA()
    Debug.Print Chr(34) & Replace(ActiveCell.FormulaR1C1, Chr(34), Chr(34) & Chr(34)) & Chr(34)
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top