Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop and and accumulate values
I need to loop by row lets say from A1 to E50 and compare values in columns
A,B and D. If those values are the same for two consecutive rows I need to added and then continue from bottom to top in order to get only one total for those common values and place them in a different area so i get a summary section Here is what I got For I = LstRowRngXXX To 7 Step -1 If Cells(I, 1).Value = Cells(I - 1, 1).Value And _ Cells(I, 2).Value = Cells(I - 1, 2).Value And _ Cells(I, 4).Value = Cells(I - 1, 4).Value Then ''''''''Add the values and put them somewhere else'''''''''''''Here is where i need help End If Next I Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop and and accumulate values
Maybe this will help: Dim rVal As Long For I = LstRowRngXXX To 7 Step -1 If Cells(I, 1).Value = Cells(I - 1, 1).Value And _ Cells(I, 2).Value = Cells(I - 1, 2).Value And _ Cells(I, 4).Value = Cells(I - 1, 4).Value Then rVal = Application.WorksheetFunction.Sum(Cells(I, 1), _ Cells(I - 1, 1), Cells(I, 2), Cells(I - 1, 2), Cells(I, 4), Cells(I - 1, 4)) Sheets("Sheet2").Range("A1").Value = rVal End If Next I LuisE;326312 Wrote: I need to loop by row lets say from A1 to E50 and compare values in columns A,B and D. If those values are the same for two consecutive rows I need to added and then continue from bottom to top in order to get only one total for those common values and place them in a different area so i get a summary section Here is what I got For I = LstRowRngXXX To 7 Step -1 If Cells(I, 1).Value = Cells(I - 1, 1).Value And _ Cells(I, 2).Value = Cells(I - 1, 2).Value And _ Cells(I, 4).Value = Cells(I - 1, 4).Value Then ''''''''Add the values and put them somewhere else'''''''''''''Here is where i need help End If Next I Thanks in advance -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=91179 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop and and accumulate values
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop and and accumulate values
I'm a fan of the Do Until statement so I would do something like this:
Range("A1").Select Do Until Activecell = "" If Activecell = Activecell(2,1) And Activecell(1,2) = Activecell(2,2) and Activecell(1,4) = Activecell(2,4) Then MyValue = Activecell + Activecell(1,2) + Activecell(1,3) 'Unsure if calc is all 6 cells or just the three being compared Activecell(2,5) = MyValue 'Puts the calc in next row down, col E Activecell(3,1).Select 'Moves to the row 3 rows down (assume you don't want to check the included row again?) Else Activecell(2,1).Select End If MyValue = 0 Loop So this will check, if it's the first iteration of the loop, A1 against A2, B1 against B2 and D1 against D2. If all three figures match then it will add up A1 + B1 + D1 and put the calculation in E2 and will then move down to C1. If the figures don't match, it will move to B1 and check B1 against C1 etc. this also assumes that there is always data in column A until the end of the data that needs to be checked. If you need to put the calcs on a different sheet then you would add Sheets("Result").Select before you put the calc anywhere. Hope this helps. "LuisE" wrote: I need to loop by row lets say from A1 to E50 and compare values in columns A,B and D. If those values are the same for two consecutive rows I need to added and then continue from bottom to top in order to get only one total for those common values and place them in a different area so i get a summary section Here is what I got For I = LstRowRngXXX To 7 Step -1 If Cells(I, 1).Value = Cells(I - 1, 1).Value And _ Cells(I, 2).Value = Cells(I - 1, 2).Value And _ Cells(I, 4).Value = Cells(I - 1, 4).Value Then ''''''''Add the values and put them somewhere else'''''''''''''Here is where i need help End If Next I Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accumulate values | Setting up and Configuration of Excel | |||
accumulate | Excel Discussion (Misc queries) | |||
accumulate totals | Excel Worksheet Functions | |||
how to accumulate value and put in other sheet | Excel Worksheet Functions | |||
how to accumulate value and put in other sheet | Excel Programming |