genracela
04-27-2010, 04:57 PM
I created a VBA code for cell formulas:
Sub calc2()
Range("AA8:AA550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")),0)"
Range("AB8:AB550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")/SUMIF(M8:P8,"">0"")),0)"
Range("AC8:AC550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")+AM8-SUM(M8:M8)),0)"
Range("AD8:AD550").Formula = "=IFERROR(((SUMIF(U8:Y8,"">0"")+AM8)/SUMIF(M8:P8,"">0"")),0)"
Range("AE8:AE550").Formula = "=IFERROR(((SUMIF(U8:Y8,"">0"")+AM8)/SUMIF(M8:T8,"">0"")),0)"
Range("AF8:AF550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")-SUMIF(M8:T8,"">0"")),0)"
Range("AG8:AG550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")-SUMIF(M8:M8,"">0"")),0)"
Range("AH8:AH550").Formula = "=IFERROR((IF(VLOOKUP(A8,Category!A:B,2,0)=1,1,IF(L8=0,1.4,IF(L8<=L$6,1,1.4)))),0)"
Range("AI8:AI550").Formula = "=IFERROR((IF(L8=0,IF(SUM(U8:Y8)+AM8-SUM(M8:T8)>0,0,SUM(U8:Y8)+AM8-SUM(M8:T8)),IF(L8<=AI$6,0,IF(SUM(U8:Y8)+AM8-SUM(M8:T8)>0,0,SUM(U8:Y8)+AM8-SUM(M8:T8))))),0)"
Range("AJ8:AJ550").Formula = "=IFERROR((ROUND(F8*AI8,0)),)"
Range("AK8:AK550").Formula = "=IFERROR((U8/(N8+O8+P8)),0)"
Range("AL8:AL550").Formula = "=IFERROR((Y8/(N8+O8+P8)),0)"
Range("AM8:AM550").Formula = "=IFERROR((IF(AND($CW8<=$DB8,$DD8<100%),$CU8,0)),0)"
Range("AN8:AN550").Formula = "=IFERROR((IF(AND($CW8>$DB8,$CW8<$DC8,$DD8<100%),$CU8,0)),0)"
Range("AO8:AO550").Formula = "=IFERROR((IF(AND($CW8>$DC8+14,$CW8<$DC8+60,$DD8<100%),$CU8,0)),0)"
Range("AP8:AP550").Formula = "=IFERROR((IF(AND($CW8>=$DC8+60,$DD8<100%),$CU8,0)),0)"
End Sub
My problem is, formulas are populated in the cells until row 550 on every column even if there are no data to calculate, and it shows "0" in every cell.
Question:
How will I modify this code when I only want to calculate the rows that has data?
How do "last row" code works?:help
Thanks in advance!
Sub calc2()
Range("AA8:AA550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")),0)"
Range("AB8:AB550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")/SUMIF(M8:P8,"">0"")),0)"
Range("AC8:AC550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")+AM8-SUM(M8:M8)),0)"
Range("AD8:AD550").Formula = "=IFERROR(((SUMIF(U8:Y8,"">0"")+AM8)/SUMIF(M8:P8,"">0"")),0)"
Range("AE8:AE550").Formula = "=IFERROR(((SUMIF(U8:Y8,"">0"")+AM8)/SUMIF(M8:T8,"">0"")),0)"
Range("AF8:AF550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")-SUMIF(M8:T8,"">0"")),0)"
Range("AG8:AG550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")-SUMIF(M8:M8,"">0"")),0)"
Range("AH8:AH550").Formula = "=IFERROR((IF(VLOOKUP(A8,Category!A:B,2,0)=1,1,IF(L8=0,1.4,IF(L8<=L$6,1,1.4)))),0)"
Range("AI8:AI550").Formula = "=IFERROR((IF(L8=0,IF(SUM(U8:Y8)+AM8-SUM(M8:T8)>0,0,SUM(U8:Y8)+AM8-SUM(M8:T8)),IF(L8<=AI$6,0,IF(SUM(U8:Y8)+AM8-SUM(M8:T8)>0,0,SUM(U8:Y8)+AM8-SUM(M8:T8))))),0)"
Range("AJ8:AJ550").Formula = "=IFERROR((ROUND(F8*AI8,0)),)"
Range("AK8:AK550").Formula = "=IFERROR((U8/(N8+O8+P8)),0)"
Range("AL8:AL550").Formula = "=IFERROR((Y8/(N8+O8+P8)),0)"
Range("AM8:AM550").Formula = "=IFERROR((IF(AND($CW8<=$DB8,$DD8<100%),$CU8,0)),0)"
Range("AN8:AN550").Formula = "=IFERROR((IF(AND($CW8>$DB8,$CW8<$DC8,$DD8<100%),$CU8,0)),0)"
Range("AO8:AO550").Formula = "=IFERROR((IF(AND($CW8>$DC8+14,$CW8<$DC8+60,$DD8<100%),$CU8,0)),0)"
Range("AP8:AP550").Formula = "=IFERROR((IF(AND($CW8>=$DC8+60,$DD8<100%),$CU8,0)),0)"
End Sub
My problem is, formulas are populated in the cells until row 550 on every column even if there are no data to calculate, and it shows "0" in every cell.
Question:
How will I modify this code when I only want to calculate the rows that has data?
How do "last row" code works?:help
Thanks in advance!