VBA - apply formula to a range of cells if adjacent cell value meets the criteria

[hrayani]

Hello Friends,

I need a VBA code to apply these formula in Columns C:F if Column M is showing > In Process

Formula for Column C

=H2-SUMIF($B$1:$B1,$B2,C$1:C1)

Formula for Column D

=I2-SUMIF($B$1:$B1,$B2,D$1:D1)

Formula for Column E

=J2-SUMIF($B$1:$B1,$B2,E$1:E1)

Formula for Column F

=K2-SUMIF($B$1:$B1,$B2,F$1:F1)

Pls Note:
1) Column M Values are formula result
2) Leave the values as it is where there condition is not met. Like where Column M does not equal to In Process then leave the values in Column C:F as it is

Any help would be appreciated

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DATE	PO #	Cutting	Stitching	Checking	Packing		Cutting	Stitching	Checking	Packing		Status
2	01-Jul	534	100	75	50	25		100	75	50	25		Shipped
3	03-Jul	525	125	100	75	50		125	100	75	50		In Process
4	05-Jul	534	50	50	50	50		150	125	100	75		In Process
5	07-Jul	525	50	50	50	50		175	150	125	100		In Process
6	09-Jul	534	50	50	50	50		200	175	150	125		Shipped
7	11-Jul	534	25	25	25	25		225	200	175	150		Shipped
Sheet1

Regards,
Humayun


EDIT:
I just realized that formulas will be applied after running the code. But what when the In Process order is shipped
So, at the end to the code if it's possible to look at the range & if any row containing formulas are shipped in Column M then it should convert those formulas to values

 

[bobsan42]

IMHO this is not a good way to work with data.
I would think you can use a pivot table (or many other ways) to summarize your data and get a proper report if your structure it properly. But messing around with it like this is not OK.
Or turn everything into smart formulas so you don't have to repeat such a task.
That's just my opinion ...

 

[hrayani]

Hi bobsan,

Thanks for the reply & suggestion.
The actual data is some 15,000 rows long and keeps on growing & the reason behind all this is to save some time as such a huge data takes long time to calculate.
After some searching on the internet & dozens of attempt I got it working with only one problem left i.e. the formula does not pick relative reference.
I have prepared a code for single column (C).

How to amend it to make it work in a way that it picks up relative reference

Sub humayun()

Dim rng As Range
 
For Each rng In Range("M2:M7")
    If rng.Value = "In Process" Then rng.Offset(0, -10).Formula = "=H2-SUMIF($B$1:$B1,$B2,C$1:C1)"
    If rng.Value = "Shipped" Then rng.Offset(0, -10).Value = rng.Offset(0, -10).Value
    Next rng
 
End Sub

 

[hrayani]

I got it working by changing the formula to R1C1 style

If rng.Value = "In Process" Then rng.Offset(0, -10).Formula = "=RC[5]-SUMIF(R1C2:R[-1]C2,RC2,R1C:R[-1]C)"

Now it is picking the relative reference