Error: Compile Error

[EdwardOcampo]

Good morning!
Trying to create this macro to place this function "=Left(B5, Find("~", B5) - 1)" into cell K5. I keep getting compile error. Any help will be appreciated.

Sub DailyServiceFormula()
Range("K5").Formula = "=Left(B5, Find("~", B5) - 1)"
End Sub

[mana]

>Find("~",

Find(""~"",

[p45cal]

Range("K5").FormulaR1C1 = "=LEFT(RC[-9], FIND(""~"", RC[-9]) - 1)"

[EdwardOcampo]

Thank you!
Works like a charm.

[EdwardOcampo]

@P45cal, thank you for the formula.
How did you get the -9? or Did excel gave it to you?

[p45cal]

@P45cal, thank you for the formula.
How did you get the -9? or Did excel gave it to you?

It is 9 columns to the left of column K since you have relative references throughout.
Yes, Excel gave it to me when I recorded a macro of me putting your formula into cell K5.