VBA inserting a Formula in a Cell Error 1004

Asked Mar 05 '20 at 17:32

Active Mar 05 '20 at 17:32

Viewed 21 times

I want to insert the following formula into a cell and drag it it down. The dragging part is done.

I keep getting Error 1004. Suggestions on how to do this?

SourceSheet.Range("k1").Formula = "=IF(IFERROR(IF(VLOOKUP(A2,Check!A:C,3,0)="",VLOOKUP('Template'!A2,Check!A:C,2,0),VLOOKUP(A2,Check!A:C,3,0)),"")="","",IFERROR(IF(VLOOKUP(A2,Check!A:C,3,0)="",VLOOKUP('Template'!A2,Check!A:C,2,0),VLOOKUP(A2,Check!A:C,3,0)),""))"

asked Mar 05 '20 at 17:32

MMMM

3

https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba – BigBen Mar 05 '20 at 17:33
4

All `"` inside the string must be doubled `""` – Scott Craner Mar 05 '20 at 17:34
2

Don't drag a formula down either - you can write it to an entire range in one line. – BigBen Mar 05 '20 at 17:34
@BigBen, how can I do that?!?! – MMMM Mar 05 '20 at 17:36
3

`SourceSheet.Range("k1:k100").Formula = ...` – Scott Craner Mar 05 '20 at 17:39
Amazing!! Never thoughts of that one, thank you everyone game changer for me! – MMMM Mar 05 '20 at 17:42
One question. How does excel update the formula reference (example A2 to A3 and so on) if the VBA code has a range with the same formula?? It worked perfectly, but I'm not sure I get the logic behind it. – MMMM Mar 05 '20 at 18:08

VBA inserting a Formula in a Cell Error 1004

0 Answers0