Error: Compile Error

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.

Code:

---

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

---

>Find("~",

Find(""~"",

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

Thank you!
Works like a charm.

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

Quote:

---

Originally Posted by EdwardOcampo

@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.