How do you insert a custom function into an excel cell using vba?

I need to put a formula into a column if the column next to it has a value.

Application.Worksheets(ws).Range("AC"&LastRow).Formula = "=IF(AB2<>"",("ABC(")&(AB2)&(")ABC(")&(Y2)&(");"), "")"

although I could use some help with the syntax.

Basically the final formula should look like

= "some text" & Cell AB2 & "some text" & Y2 & "some text"

if AB2 has a value. In the code above I get a end of statement error in VBA which I am guessing is because of all the parentheses.

If anyone can help me with this I'd appreciate it.

Hi 111StepsAhead,

I make the formula work in the cell not using VBA first. Then copy the formula to the VBA editor.
Put quotes in front and the rear and double each quote in the rest of the formula.
I've spent hours trying to build formulas using VBA and the above is how I've solved many of the syntax errors.

I hope this helps.

Yes, thank you MarvinP. That solved my first issue. However it brings to light a new problem. A type mismatch that occurs with the entire line.

Application.Worksheets(ws).Range("AC" & LastRow).Formula = "=IF(AB2<>"""",""ABC(""&AB2&"")ABC(""&Y2&"");"", "")"

I am looking for a solution but in if anyone knows the answer I'd appreciate it.

EDIT: Forgot to double quote the if false statement. Thanks again Marvin.