VBA Sumifs

[hrayani]

Hi friends,

How to write this formula in VBA....

=IF(AND($C$1="ALL",$C$2="ALL"),SUMIFS(orders_quantity,orders_po_shipment_date,">="&F$2,orders_po_shipment_date,"<="&F$4,orders_ref,$B11),IF(AND($C$1="ALL",$C$2<>"ALL"),SUMIFS(orders_quantity,orders_supplier,$C$2,orders_po_shipment_date,">="&F$2,orders_po_shipment_date,"<="&F$4,orders_ref,$B11),IF(AND($C$1<>"ALL",$C$2="ALL"),SUMIFS(orders_quantity,orders_customer,$C$1,orders_po_shipment_date,">="&F$2,orders_po_shipment_date,"<="&F$4,orders_ref,$B11),IF(AND($C$1<>"ALL",$C$2<>"ALL"),SUMIFS(orders_quantity,orders_supplier,$C$2,orders_customer,$C$1,orders_po_shipment_date,">="&F$2,orders_po_shipment_date,"<="&F$4,orders_ref,$B11),""))))

I only want the result not the formula in range H11 to H403.

Regards,

Humayun

 

[StephenCrump]

You could do it like this:

With Range("H11:H403")
    .Formula = "=IF(AND($C$1=""ALL"",$C$2=""ALL""),SUMIFS(orders_quantity,orders_po_shipment_date,"">=""&F$2,orders_po_shipment_date,""<=""&F$4,orders_ref,$B11),IF(AND($C$1=""ALL"",$C$2<>""ALL""),SUMIFS(orders_quantity,orders_supplier,$C$2,orders_po_shipment_date,"">=""&F$2,orders_po_shipment_date,""<=""&F$4,orders_ref,$B11),IF(AND($C$1<>""ALL"",$C$2=""ALL""),SUMIFS(orders_quantity,orders_customer,$C$1,orders_po_shipment_date,"">=""&F$2,orders_po_shipment_date,""<=""&F$4,orders_ref,$B11),IF(AND($C$1<>""ALL"",$C$2<>""ALL""),SUMIFS(orders_quantity,orders_supplier,$C$2,orders_customer,$C$1,orders_po_shipment_date,"">=""&F$2,orders_po_shipment_date,""<=""&F$4,orders_ref,$B11),""""))))"
    .Value = .Value
End With

 

[hrayani]

Hi Stephen,

Thanks for the solution... Its working OK

But, its taking more or less same time as with the formulas in there. Is there a better way to handle it - to make it work faster

Regards,

Humayun

 

[Michael M]

Have you turned off screenupdating and turned calculation to manual ?

 

[hrayani]

Hello Micheal,

Yes I did at the start of the code and turned it back on at the end of the code.

Still with formulas in there works a bit faster as compared to VBA..

Regards,

Humayun