VBA to insert formula and then fill down number of rows in reference column

[Satos]

Hi everybody,

I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:

Sheets("Sold Articles Database").Select
Range("U3").Formula = " =VLOOKUP(LEFT(K3,2),'Input Variables'!$A$48:$B$52,2,FALSE)"
Range("V3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$C$10000,3,FALSE)"
Range("W3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$D$10000,4,FALSE)"
Range("X3").Formula = "=IFERROR(IF(VLOOKUP(K3,'Product datas'!$A$2:$H$10000,8,FALSE)="","",VLOOKUP(K3,'Product datas'!$A$2:$H$10000,8,FALSE)),"")"
Range("Y3").Formula = "=IFERROR(IF(VLOOKUP(K3,'Product datas'!$A$2:$I$10000,9,FALSE)="","",VLOOKUP(K3,'Product datas'!$A$2:$I$10000,9,FALSE)),"")"
Range("Z3").Formula = " =VLOOKUP(K3,'Product datas'!$A$2:$E$10000,5,FALSE)"
Range("AA3").Formula = "=IF(LEFT(A3,3)=""MBE"",B3,(DATE(LEFT(B3,4),LEFT(RIGHT(B3,5),2),RIGHT(B3,2))))"
Range("AB3").Formula = "=+WEEKNUM(AA3-1)"
Range("AC3").Formula = "=VLOOKUP(AB3,'Input Variables'!$A$69:$B$121,2)"
Range("AD3").Formula = " =IFERROR(IF(LEFT(A3,3)=""MBE"",R3,(DATE(LEFT(R3,4),(RIGHT(LEFT(R3,7),2)),RIGHT(LEFT(R3,10),2)))),"")"
Range("AE3").Formula = "=+WEEKNUM(AD3-1)"
Range("AF3").Formula = "=IFERROR(VLOOKUP(AE3,'Input Variables'!$A$69:$B$121,2),"")"
Range("AG3").Formula = "=IF(LEFT(A3,3)=""MBE"",VLOOKUP(A3,'Afterbuy modified'!$A$3:$U$5000,21,FALSE),""Shop"")"
Range("AH3").Formula = "=M3*N3"
Range("AI3").Formula = "=-M3*SUBSTITUTE(S3,"","",""."")"
Range("AJ3").Formula = "=AH3+AI3"
Range("AK3").Formula = "=+AJ3/(1+'Input Variables'!$B$13)"
Range("AL3").Formula = "=VLOOKUP(K3,'Product datas'!$A$2:$F$10000,6,FALSE)*N3"
Range("AM3").Formula = "=+VLOOKUP(Z3,'Input Variables'!$A$55:$B$65,2,FALSE)"
Range("AN3").Formula = "=+(1-AM3)*AL3"
Range("AO3").Formula = "=AK3-AN3"
Range("AP3").Formula = "=AO3/AK3"
Range("AQ3").Formula = "=(VLOOKUP(P3,'Input Variables'!$A$19:$C$35,2,FALSE)*AH3)+VLOOKUP(P3,'Input Variables'!$A$19:$C$35,3,FALSE)"
Range("AR3").Formula = "=VLOOKUP(K3,'Product datas'!$A$2:$G$10000,7,FALSE)*N3"
Range("AS3").Formula = "=AO3-AQ3-AR3"
Range("AT3").Formula = "=AS3/AK3"
Range("AU3").Formula = "=VLOOKUP(AC3,'Marketing Export'!$G$3:$I$55,3,FALSE)"
Range("AV3").Formula = "=AS3-AU3"
Range("AW3").Formula = "=AV3/AK3"
Range("AX3").Formula = "=VLOOKUP(K3,'Product datas'!$A$2:$E$10000,5,FALSE)&"" ""&VLOOKUP(K3,'Product datas'!$A$2:$E$10000,2,FALSE)"
Range("AY3").Formula = " =IF(T3<0,""Afterbuy cancellation"",IF(D3=""Test"",""Test"",""""))"
Range("BA3").Formula = "=A3"
Range("BB3").Formula = "=AC3"
Range("BC3").Formula = "=U3"
Range("BD3").Formula = "=AX3"
Range("BE3").Formula = "=AY3"
Range("BF3").Formula = "=V3"
Range("BG3").Formula = "=W3"
Range("BH3").Formula = "=X3"
Range("BI3").Formula = "=Z3"
Range("BJ3").Formula = "=N3"
Range("BK3").Formula = "=Y3"
Range("BL3").Formula = "=AH3"
Range("BM3").Formula = "=AJ3"
Range("BN3").Formula = "=AK3"

Sheets("Sold Articles Database").Select
Range("U3:AY3").AutoFill Destination:=Range("U3:AY" & Cells(Rows.Count, "A").End(xlUp).Row)

Could anyone help me with this problem. I start getting an error in the formula with red.
Thanks in advance

 

[VoG]

You need to double up the quotes inside the formula

Range("X3").Formula = "=IFERROR(IF(VLOOKUP(K3,'Product datas'!$A$2:$H$10000,8,FALSE)="""","""",VLOOKUP(K3,'Product datas'!$A$2:$H$10000,8,FALSE)),"""")"

 

[Satos]

Thanks VoG,

I have first changed the formula to:
Range("X3").Formula = "=IFERROR(VLOOKUP(K3,'Product datas'!$A$2:$H$10000,8,FALSE),0)"

and now I get 0 values if the value is not found. Now I want to get blank cell if the value is not found.

Range("X3").Formula = "=IFERROR(VLOOKUP(K3,'Product datas'!$A$2:$H$10000,8,FALSE),"""")"

But the formula does not return a blank cell but i get 0 as the result.
I have copied the formula to another cell and it doesn't work still.

Do you know what is the reason for this problem?

 

[VoG]

Sorry, I don't know why that would give 0