How do I Sum variables

[Novaloc]

Hi All,
This is only a small part of the spreadsheet
I have a component Name in Column C2:C20 and quantity in Column D220.
ColumnC can contain many components of the same name.
I have managed with VBA code ( Thanks to Colo's Excel Junk Room) to put all
unique component names in ColumnC starting at C22.
What I can't seem to be able to do is sum the unique totals from D220 for
the unique names for Column D starting at D22
The code below works for individual Names. There are a possible 150 part
names so I reckon that there would be a way with a For... Each... routine or
a loop. But, I can't get either of them to produce the correct result.
Also there is no way of knowing what part name will be at what address
Can anyone help?
Dim cn
Dim tally
Dim Qty
For Each cn In myRange3
If cn = "6T2" Then
Qty = cn.Offset(0, 1)
tally = tally + a3
End If
Next
Range("D22").Value = tally


Thanks
Richard

 

[jay]

One way would be to simply use a formula in cell D22 like this:
=SUMIF($C$2:$C$20,C22,$D$2:$D$20)
Then copy that formula down next to all the unique names below it.

You could take this formula approach a step further by creating the
formulas through VBA:
for x = 22 to 100
if range("C" & x).value <> "" then
range("D" & x).FormulaR1C1 =
"=SUMIF(R2C3:R20C3,RC[-1],R2C4:R20C4)"
end if
next x

Or you could take your For... Each approach by just wrapping it in
another loop:
for each cn2 in myRange4 '(this range would be all your unique names in
C22 to C42)
tally = 0
if cn2 <> "" then
'here is your code a little modified
for each cn in myRange3
if cn = cn2 then
qty = cn.offset(0,1)
tally = tally + qty
end if
next cn
range("D" & cn2.row).value = tally
end if
next cn2

 

[Rowan Drummond]

How about using your VBA code to add sumif formulas:

Sub sumit()
Dim eRow As Long
eRow = Cells(Rows.Count, 3).End(xlUp).Row
Range(Cells(22, 4), Cells(eRow, 4)).Formula = _
"=sumif($C$2:$C$20,C22,$D$2:$D$20)"
End Sub

Or if you only want the values you can use:

Sub sumit()
Dim eRow As Long
eRow = Cells(Rows.Count, 3).End(xlUp).Row
With Range(Cells(22, 4), Cells(eRow, 4))
.Formula = "=sumif($C$2:$C$20,C22,$D$2:$D$20)"
.Value = .Value
End With
End Sub

Hope this helps

 

[Richard]

Jay,

Thanks for your response.

The re-worked VBA code works like magic
Many Thanks

Richard