VBA Loop through column, count multiple different values and output total of each in a cell

[DrParmeJohnson]

Hello, I'm quite awful at VBA and am trying to make a macro that does this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Item#[/TD]
[TD]Item Name[/TD]
[TD]Qty[/TD]
[TD]Total[/TD]
[TD]Tracking[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]xxxx[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]xxxx[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]xxxx[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]xxxx[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]xxxx[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]xxxx[/TD]
[/TR]
</tbody>[/TABLE]

For this format, with the letters on top being default excel columns, I am trying to have the macro go through column E (Item#) and column G (Qty), look for similar Item#'s (i.e 1000) and add up the value in Qty, so for Item# 1000, 3 and output that total in the last cell of the row, in Column H, containing Item# 1000. It would then continue looping through until the end of the Item# column.

I would assume this might be done using like assigned ranges, end of row, and a sum function all bundled into a loop but, I lack the knowledge to do so. So, if someone can assist me in doing this, I would greatly appreciate the assistance.

If there is any need of clarification, as I tend to be bad at explaining things, please do let me know.

Thank you,
DPJ

 

[kweaver]

There's probably a better way, but I had an urgent need recently to do something like this and stumbled through some VBA and adjusted mine to your need.
I think this will work for yours:

Sub InsertTotalAtItemChange()
Dim Rng As Range, LR As Long, i As Integer, j As Integer
Dim WorkRng As Range
LR = Cells(Rows.Count, "E").End(xlUp).Row
Range("E1:I" & LR).Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlYes
On Error Resume Next
Set WorkRng = Range("E2:E" & LR)
Application.ScreenUpdating = False

Range("H2:H" & LR) = ""
j = 2
For i = 2 To LR
 If Cells(i, 5) <> Cells(i + 1, 5) Then
 Cells(i, 8).Value = Application.WorksheetFunction.Sum(Range("G" & j & ":G" & i))
 j = i + 1
 End If
Next i
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Assuming ..
- Column E is sorted so all like Item# values are grouped together, and
- Those headings are in row 1
.. then you could try this

Sub Sum_Qty()
  With Range("H2:H" & Range("E" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(E2=E3,"""",SUMIF(E$2:E2,E2,G$2:G2))"
    .Value = .Value
  End With
End Sub

If the item numbers are not sorted and you just want the total on the last row of an item number then change the formula line above to

.Formula = "=IF(COUNTIF(E2:E$" & Range("E" & Rows.Count).End(xlUp).Row & ",E2)=1,SUMIF(E$2:E2,E2,G$2:G2),"""")"

 

[DrParmeJohnson]

Worked like a charm, so sorry about the delay. It was a work thing and I'm not in on the weekend.

Thank you for your help.

 

[Peter_SSs]

Worked like a charm, ..

Not sure which of the 3 options you are referring to but glad you got something suitable for your needs.