Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding 2 cells only if one is not over | Excel Discussion (Misc queries) | |||
Need help w/ adding cells and dividing SUM by number of cells | Excel Worksheet Functions | |||
adding more than one value to cells | Excel Worksheet Functions | |||
I need help adding cells together? | Excel Worksheet Functions | |||
Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) |