VBA - adding one formula works, but the other doesn't?

[Mr2017]

Hi

I've got a script that adds a formula to a cell and drags it down.

However, I'm stumped by the fact that one formula works, but the other doesn't!

So this Sub works (where the last line adds and drags the formula : "=IFERROR((AY16/K16)-AF16,"""")"

But the formula at the end of the second Sub isn't being accepted by the VB editor?

I tried to replace the single quotes with double quotes, but that didn't work? Is there any obvious reason why the VB editor wouldn't accept it?

Workbooks(Main sheet).Sheets("Main sheet").Range("DD16").Formula = Workbooks(This_Bk).Sheets("Data").Range("P2").Formula

Dim rowBottom As Integer
rowBottom = Range("DC" & Rows.Count).End(xlUp).Row




Range("DD16:DD" & rowBottom).Formula = "=IFERROR((AY16/K16)-AF16,"""")"

But this one doesn't?

Workbooks(Main sheet).Sheets("Main sheet").Range("DD16").Formula = Workbooks(This_Bk).Sheets("Data").Range("P2").Formula

Dim rowBottom As Integer
rowBottom = Range("DC" & Rows.Count).End(xlUp).Row




Range("DD16:DD" & rowBottom).Formula = "=IFERROR(IF(H16="""","""",IF(BJ16="DIA",(((((IF(BG16="MINER",(IF(AND(BI16="FSA (CUMUL)",(OR(BF16="DATA",BF16="UNIT"))),(((AU16/K16)/L16)-(BE16/T16)),IF(AND(BI16="ABC",BF16="DATA"),((((AU16/K16)/L16)-( BE16/T16)/K16)),IF(AND(BI16="ABC",BF16="Pack"),(((AU16/K16)/L16)-(BE16/K16)),"CHECK/FETCH")))),IF(BG16="GBP",(IF(AND(BI16="FSA (CUMUL)",(OR(BF16="DATA",BF16="UNIT"))),((((AU16/K16)/L16)-(BE16/T16))*BH16),IF(AND(BI16="ABC",BF16="DATA"),(((AU16/K16)/L16)* BH16-((BE16/T16)/K16)),IF(AND(BI16="ABC",BF16="PACK"),((((AU16/K16)/L16)-(BE16/K16))*BH16),"CHECK/FETCH"))))))))))),((((IF(BG16="MINER",(IF(AND(BI16="FSA (CUMUL)",(OR(BF16="DATA",BF16="UNIT"))),((AU16/K16)-(BE16/T16)),IF(AND(BI16="ABC",BF16="DATA"),(((AU16/K16)-(BE16/T16)/K16)),IF(AND(BI16="ABC",BF16="Pack"),((AU16/K16)-(BE16/K16)),"CHECK/FETCH")))),IF(BG16="GBP",(IF(AND(BI16="FSA (CUMUL)",(OR(BF16="DATA",BF16="UNIT"))),(((AU16/K16)-(BE16/T16))*BH16),IF(AND(BI16="ABC",BF16="DATA"),((AU16/K16)*BH16-((BE16/T16)/K16)),IF(AND(BI16="ABC",BF16="PACK"),(((AU16/K16)-(BE16/K16))*BH16),"CHECK/FETCH")))))))))))),"""")"

 

[Fluff]

You haven't doubled-up all the quotes inside the formula string.

 

[Mr2017]

I'd tried that, but and have just tested it again (replaced all the single quotes with double quotes, including the single quotes around words).

But what is strange is that when I go to insert the long formula in the VB editor, even though I insert it between double quotes, the VB editor moves the formula over to the next line and makes the formula red. PLUS it closes the quotes!

So I originally had something that looked like this, after which I inserted the long formula.

Sub
Range("DD16:DD" & rowBottom).Formula = "
End Sub

However, the VB editor moved down the formula I inserted on to the next row and added double quotes to the end of the code above ie the code above became this:

Sub 
Range("DD16:DD" & rowBottom).Formula = ""
End Sub

(please note the additional quote at the end)

Do you know why this would happen?

Also, to shed any doubt, please find below the formula with the doubled up quotes - please let me know if you'd like me to clarify anything further? TIA

"=IFERROR(IF(H16="""","""",IF(BJ16=""DIA"",(((((IF(BG16=""MINER"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),(((AU16/K16)/L16)-(BE16/T16)),IF(AND(BI16=""ABC"",BF16=""DATA""),((((AU16/K16)/L16)-( BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""Pack""),(((AU16/K16)/L16)-(BE16/K16)),""CHECK/FETCH"")))),IF(BG16=""GBP"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),((((AU16/K16)/L16)-(BE16/T16))*BH16),IF(AND(BI16=""ABC"",BF16=""DATA""),(((AU16/K16)/L16)* BH16-((BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""PACK""),((((AU16/K16)/L16)-(BE16/K16))*BH16),""CHECK/FETCH""))))))))))),((((IF(BG16=""MINER"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),((AU16/K16)-(BE16/T16)),IF(AND(BI16=""ABC"",BF16=""DATA""),(((AU16/K16)-(BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""Pack""),((AU16/K16)-(BE16/K16)),""CHECK/FETCH"")))),IF(BG16=""GBP"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),(((AU16/K16)-(BE16/T16))*BH16),IF(AND(BI16=""ABC"",BF16=""DATA""),((AU16/K16)*BH16-((BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""PACK""),(((AU16/K16)-(BE16/K16))*BH16),""CHECK/FETCH"")))))))))))),"""")"

 

[RoryA]

Your formula is too long for one line, so you need to split it over a few:

   ActiveCell.Formula = _
"=IFERROR(IF(H16="""","""",IF(BJ16=""DIA"",(((((IF(BG16=""MINER"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT"")))" & _
",(((AU16/K16)/L16)-(BE16/T16)),IF(AND(BI16=""ABC"",BF16=""DATA""),((((AU16/K16)/L16)-( BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""Pack"")," & _
"(((AU16/K16)/L16)-(BE16/K16)),""CHECK/FETCH"")))),IF(BG16=""GBP"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT"")))," & _
"((((AU16/K16)/L16)-(BE16/T16))*BH16),IF(AND(BI16=""ABC"",BF16=""DATA""),(((AU16/K16)/L16)* BH16-((BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""PACK"")," & _
"((((AU16/K16)/L16)-(BE16/K16))*BH16),""CHECK/FETCH""))))))))))),((((IF(BG16=""MINER"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT"")))," & _
"((AU16/K16)-(BE16/T16)),IF(AND(BI16=""ABC"",BF16=""DATA""),(((AU16/K16)-(BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""Pack""),((AU16/K16)-(BE16/K16)),""CHECK/FETCH""))))" & _
",IF(BG16=""GBP"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),(((AU16/K16)-(BE16/T16))*BH16),IF(AND(BI16=""ABC"",BF16=""DATA"")," & _
"((AU16/K16)*BH16-((BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""PACK""),(((AU16/K16)-(BE16/K16))*BH16),""CHECK/FETCH"")))))))))))),"""")"

 

[Fluff]

When the formula is that long, you need to break it down into separate lines like

Range("DD16:DD" & rowBottom).Formula = "=IFERROR(IF(H16="""","""",IF(BJ16=""DIA"",(((((IF(BG16=""MINER"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),(((AU16/K16)/L16)-(BE16/T16)),IF(AND(BI16=""ABC"",BF16=""DATA""),((((AU16/K16)/L16)-( BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""Pack""),(((AU16/K16)/L16)-(BE16/K16)),""CHECK/FETCH""))))," & _
    "IF(BG16=""GBP"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),((((AU16/K16)/L16)-(BE16/T16))*BH16),IF(AND(BI16=""ABC"",BF16=""DATA""),(((AU16/K16)/L16)* BH16-((BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""PACK""),((((AU16/K16)/L16)-(BE16/K16))*BH16),""CHECK/FETCH""))))))))))),((((IF(BG16=""MINER"",(IF(AND(BI16=""FSA (CUMUL)""," & _
    "(OR(BF16=""DATA"",BF16=""UNIT""))),((AU16/K16)-(BE16/T16)),IF(AND(BI16=""ABC"",BF16=""DATA""),(((AU16/K16)-(BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""Pack""),((AU16/K16)-(BE16/K16)),""CHECK/FETCH"")))),IF(BG16=""GBP"",(IF(AND(BI16=""FSA (CUMUL)"",(OR(BF16=""DATA"",BF16=""UNIT""))),(((AU16/K16)-(BE16/T16))*BH16),IF(AND(BI16=""ABC"",BF16=""DATA"")," & _
    "((AU16/K16)*BH16-((BE16/T16)/K16)),IF(AND(BI16=""ABC"",BF16=""PACK""),(((AU16/K16)-(BE16/K16))*BH16),""CHECK/FETCH"")))))))))))),"""")"

 

[Mr2017]

Ok, cool - thanks - I didn’t know that!

I will test this out tomorrow.

Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Mr2017]

Thank you - I didn’t know that!

I thought you could just paste it into the VB editor.

But I’ll try this out tomorrow.

Thanks.

 

[Mr2017]

No problem. You’re welcome.

And thank you!