Works as formula but doesn't work as VBA

[Pete2211]

Hi.
I have the below code which isn't working in VBA.
The formula works perfectly as a formula placed manually in the cell but doesn't work as part of the macro.
It seems to be the "" at the end of the formula which is confusing it. Please can somebody help?

    Sub FillEXTEND()
Sheets("OVERDUE").Select
    range("M1").Select
Dim LastRowColumnJ As Long
LastRowColumnJ = Cells(Rows.Count, 1).End(xlUp).Row
range("M2:M" & LastRowColumnJ).Formula = "=IF(and(J2<=TODAY(),L2<=Today(),ROUND((TODAY()-L2)/7,1),[U][B][SIZE=4][I]""[/I][/SIZE][/B][/U])"
End Sub

As you can see from the coding, I'm an absolute beginner at this so please bare with me.

 

[Fluff]

Hi & welcome to MrExcel
You need to double up on the quotes so instead of "" you need """"

 

[Pete2211]

Hi Fluff.

That doesn't work either but thank you for trying for me.
By the way, my nickname is Fluff and I live just 20 miles up the M4 from your location

 

[Fluff]

Looks like you're missing a closing bracket as well.

 

[Pete2211]

Once again, many thanks. I really appreciate you taking time out of your busy day to look at this.
I spotted the missing closing bracket at the end of the AND statement but can't see any others yet it still isn't working
Unfortunately, the error code is the ambiguous '1004' so that doesn't help either of us with any clues

 

[Fluff]

What is the message that goes with the code?

 

[Fluff]

Also can you post the amended formula and your variable is LastRowColumnJ, but you are looking in col A for the last row.

 

[shg]

...Formula = "=IF(AND(J2 <= TODAY(), L2 <= TODAY(), ROUND((TODAY() - L2) / 7, 1)), """")"

 

[Pete2211]

Hi
The error message is 'Application-defined or Object-defined error

    Sub FillEXTEND()
Sheets("OVERDUE").Select
    range("M1").Select
Dim LastRowColumnJ As Long
LastRowColumnJ = Cells(Rows.Count, 1).End(xlUp).Row
range("M2:M" & LastRowColumnJ).Formula = "=IF(and(J2<=TODAY(),L2<=Today()),ROUND((TODAY()-L2)/7,1),"""")"
End Sub

I'll pick this thread up tomorrow, now.
Thank you very much for trying to help with this.

 

[Fluff]

Your code works for me.
What line is highlighted when you click debug?
Do you have any merged cells & is the sheet protected?