Adding certain cells
 

Hi,

I have two ranges, A1:A5, B1:B5.

I want to have a macro that looks up all the values in the 1st range and add
all the cells in 2nd range that match in the 1st range and have a msgbox
output the totals.

For Example:

A B
10 1
5 2
15 3
10 4
15 5

Output:

10 5
5 2
15 8

Thanks.

Adding certain cells
 

Here is a start:

Range("A1:A6").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("E1"), Unique:=True
Range("F2").Select
ActiveCell.FormulaR1C1 =
"=SUMIF(RC[-5]:R[4]C[-5],RC[-1],RC[-4]:R[4]C[-4])"
Range("F2").Select
Range("F2").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row + 1
Range("F2").Select
Do Until ActiveCell.Row = lastrow
MsgBox ("Total: " & ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop

Adding certain cells
 

Maybe it's my XL97, but I didn't get this to work.
Here's a variation using your idea:
Sub sub1()
Dim iRow&, s$
Range("d1") = "Header?"
Range("a1:b5").Copy Range("d2:e6")
Range("d1:d6").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("f1"), Unique:=True
iRow = 2
Do Until Cells(iRow, 6) = ""
s = "=SumIf(d2:d6, " & Cells(iRow, 6).Address & ",e2:e6)"
Cells(iRow, 7) = s
MsgBox Cells(iRow, 6) & ": " & Cells(iRow, 7)
iRow = iRow + 1
Loop
End Sub

Chip wrote:
Here is a start:
Range("A1:A6").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("E1"), Unique:=True
Range("F2").Select
ActiveCell.FormulaR1C1 =
"=SUMIF(RC[-5]:R[4]C[-5],RC[-1],RC[-4]:R[4]C[-4])"
Range("F2").Select
Range("F2").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row + 1
Range("F2").Select
Do Until ActiveCell.Row = lastrow
MsgBox ("Total: " & ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop

JN wrote:
I have two ranges, A1:A5, B1:B5.
I want to have a macro that looks up all the values in the 1st range
and add all the cells in 2nd range that match in the 1st range
and have a msgbox output the totals.
For Example:
A B
10 1
5 2
15 3
10 4
15 5
Output:
10 5
5 2
15 8

Adding certain cells
 

Maybe it's my XL97, but I didn't get this to work.
Here's a variation using your idea:
Sub sub1()
Dim iRow&, s$
Range("d1") = "Header?"
Range("a1:b5").Copy Range("d2:e6")
Range("d1:d6").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("f1"), Unique:=True
iRow = 2
Do Until Cells(iRow, 6) = ""
s = "=SumIf(d2:d6, " & Cells(iRow, 6).Address & ",e2:e6)"
Cells(iRow, 7) = s
MsgBox Cells(iRow, 6) & ": " & Cells(iRow, 7)
iRow = iRow + 1
Loop
End Sub

Chip wrote:
Here is a start:
Range("A1:A6").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("E1"), Unique:=True
Range("F2").Select
ActiveCell.FormulaR1C1 =
"=SUMIF(RC[-5]:R[4]C[-5],RC[-1],RC[-4]:R[4]C[-4])"
Range("F2").Select
Range("F2").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row + 1
Range("F2").Select
Do Until ActiveCell.Row = lastrow
MsgBox ("Total: " & ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop

JN wrote:
I have two ranges, A1:A5, B1:B5.
I want to have a macro that looks up all the values in the 1st range
and add all the cells in 2nd range that match in the 1st range
and have a msgbox output the totals.
For Example:
A B
10 1
5 2
15 3
10 4
15 5
Output:
10 5
5 2
15 8

Adding certain cells
 

Dave D C, Thanks for your help but can you have the output show the
consolidated totals?

This is what i get when i run the macro:

10 5
5 2
15 3
15 5


I was looking for
10 5
5 2
15 8


Regards,
JN



"Dave D-C" wrote in message
...
Maybe it's my XL97, but I didn't get this to work.
Here's a variation using your idea:
Sub sub1()
Dim iRow&, s$
Range("d1") = "Header?"
Range("a1:b5").Copy Range("d2:e6")
Range("d1:d6").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("f1"), Unique:=True
iRow = 2
Do Until Cells(iRow, 6) = ""
s = "=SumIf(d2:d6, " & Cells(iRow, 6).Address & ",e2:e6)"
Cells(iRow, 7) = s
MsgBox Cells(iRow, 6) & ": " & Cells(iRow, 7)
iRow = iRow + 1
Loop
End Sub

Chip wrote:
Here is a start:
Range("A1:A6").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("E1"), Unique:=True
Range("F2").Select
ActiveCell.FormulaR1C1 =
"=SUMIF(RC[-5]:R[4]C[-5],RC[-1],RC[-4]:R[4]C[-4])"
Range("F2").Select
Range("F2").Select
Selection.End(xlDown).Select
lastrow = ActiveCell.Row + 1
Range("F2").Select
Do Until ActiveCell.Row = lastrow
MsgBox ("Total: " & ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop

JN wrote:
I have two ranges, A1:A5, B1:B5.
I want to have a macro that looks up all the values in the 1st range
and add all the cells in 2nd range that match in the 1st range
and have a msgbox output the totals.
For Example:
A B
10 1
5 2
15 3
10 4
15 5
Output:
10 5
5 2
15 8

Adding certain cells
 

Dave D-C...Thanks for fixing up my code, I misread his original post.
Dave's code works for me (it doesnt repeat the the 15)...it is doing
consolidated totals otherwise the 10 wouldnt be showing a 5.

Adding certain cells
 

Your right about the 10 but the 15 show up twice for me...strange...I am
using Office 2003 btw

"Chip" wrote in message
ups.com...
Dave D-C...Thanks for fixing up my code, I misread his original post.
Dave's code works for me (it doesnt repeat the the 15)...it is doing
consolidated totals otherwise the 10 wouldnt be showing a 5.

Adding certain cells
 

It is hard for me to do any testing since I dont get an error, but go
into the module, and hit F8, and it will step you through the code, and
maybe you can see whats wrong.

Adding certain cells
 

I ended just writing my own...this one seems to work too, atleast for me.

Thanks for your help guys.

Regards,
JN

Sub demo1()

Dim v()
v = Range("a1:b5")

For i = LBound(v, 1) To UBound(v, 1)
For j = i + 1 To UBound(v, 1)
If v(i, 1) = v(j, 1) Then
v(i, 2) = v(i, 2) + v(j, 2)
v(j, 1) = ""
v(j, 2) = ""
End If
Next j
Next i

For i = LBound(v, 1) To UBound(v, 1)
If v(i, 1) < "" Then
Cells(i, 4) = v(i, 1)
Cells(i, 5) = v(i, 2)
End If
Next i

End Sub


"Chip" wrote in message
ups.com...
It is hard for me to do any testing since I dont get an error, but go
into the module, and hit F8, and it will step you through the code, and
maybe you can see whats wrong.