Run-time error '1004';

Question

I was trying to copy the formula from N39 up to the last row but I'm always getting a Run-time error '1004' refer to this line:

n.formula = "=IF(AND(ISNUMBER(K39),ISNUMBER(L39))=TRUE,IF(ISNUMBER(M39),(K39-L39)*M39,(K39-L39)),"")"

However, when I tried changing the formula to something simpler, like =L39-K39, the code works fine.

I would appreciate any help troubleshooting my code.

Here's my code

Sub FillDownFormula()
Dim LastRow As Long
Dim n As Range

LastRow = Cells(Rows.Count, "I").End(xlUp).Row

Set n = Range("N39:N" & LastRow)
n.formula = "=IF(AND(ISNUMBER(K39),ISNUMBER(L39))=TRUE,IF(ISNUMBER(M39),(K39-L39)*M39,(K39-L39)),"")"

Set n = Nothing

End Sub

thanks.

Answer 1

You must double-up on the double quotes:

n.formula = "=IF(AND(ISNUMBER(K39),ISNUMBER(L39))=TRUE,IF(ISNUMBER(M39),(K39-L39)*M39,(K39-L39)),"""")"

Answer 2

There are a couple of syntax errors in your formula.

One of them is the "" used as your false return value is terminating your n.formula string early.

When you want to use a double quote (") inside a string that delimited by double quotes, you need to escape the internal quotes by doubling them up.

Ex:

myString = "This is a "string"." - Would fail, or at best myString would contain This is a.

Fixed by escaping the quotes:

myString = "This is a ""string""." - will contain This is a "string"..

Also, your True return value for the first IF is another IF statement. That inner IF statement has its own commas to separate the parts, but since you haven't encapsulated that statement Excel may/will think its commas are part of the outer IF statement.

So encapsulate that inner IF with brackets.

Here's the corrected formula (haven't actually tested it with data, but it passes debugging now):

=IF(AND(ISNUMBER(K39),ISNUMBER(L39))=TRUE, (IF(ISNUMBER(M39), (K39-L39)*M39, (K39-L39))), """")