Macro to run formula based on a range of data in some rows of a column

I need to develop code for a macro (or better yet a private sub that does it all):
Sample sheet attached.

Some background.

Rows are grouped (I have colored the groups to aid in understanding) by the value in Column E. (Tag 1). Each group can have from 1 to 8 duplicate values (Duplicate tag 1’s)

What I am trying to do is create some formulas in Columns O,P,Q&R ON THE LAST ROW OF DATA FOR THAT TAG1). (real life examples are located in Row 2)

For example: In Column O, I want to add the values for M for only those rows with that group’s tag number.

I have manually inserted formulas in Cells O3,O10,015,019 &O21 just to demonstrate how I would like things to look in that particular column when I am done.

It seems to me that the logic might be Keep track of the count of row E until the value changes to a non-duplicate and then use the result of that count to define the # rows in Column M to sum and display in the appropriate cell in Column O. A mouthful that is well beyond my level of expertise.

Any suggestions would be greatly appreciated.

I was able to fix the "Count" issue you asked about in Column R, but I find one other problem that I can't figure out how to fix. (I keep getting "(" expected or "End of Statement Expected,

In the line beginning " .Cells(i, 16) The expected result (for Row 10 [i=10])should be:
=IF(G10=0,"",IF((O10/G10>=90%),"",O10-(G10*0.9)))

What the code delivers, however is "=IF(G10=0,"",IF((O10/G10>=90%),"",O3-(G10*0.9)))" The code is fixed at column O3 rather than O" & i & "-G" & i

I can see and describe the problem, just can't get the syntax right to fix it.



Sub Evaluate()
Dim i As Long, iSrt As Long
With Worksheets("Inventory")
iSrt = 2
For i = iSrt To .Cells(.Rows.Count, 1).End(xlUp).Row
If .Cells(i, 5).Value <> .Cells(i + 1, 5).Value Then
.Cells(i, 15).Formula = "=SUM(M" & iSrt & ":M" & i & ")"
.Cells(i, 16).Formula = "=IF(G" & i & "=0,"""",IF((O" & i & "/G" & i & ">=90%),"""",O3-(G" & i & "*0.9)))"

.Cells(i, 17).Formula = "=IF(G" & i & "=0,"""",IF((O" & i & "/G" & i & ">100%), O" & i & "-G" & i & ",""""))"
.Cells(i, 18).Formula = "=Count(M" & iSrt & ":M" & i & ")"
iSrt = i + 1
End If
Next i
End With

That does it indeed, and thanks for the help.

Now a different issue along the same line.

I also would like some code that does the same select, but formats the cell groups alternatively "No Fill" and Fill with light blue. Can you send me in the right direction....could it even be appended to the code you already provided?

Thanks, Again

I will now mark this problem solved, even though I hope you will respond with some code

Perfecto!!!

Danke