Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through every other and 3rd cell in range
HI all
I need to create a loop that will subtract the value in every other cell in one range (lets say sheet1 a1 to a100) from the value in every third cell in another range (lets say sheet2 b1 to b150). the output should be the sum of all the subtractions given in a cell lets say sheet1 c1. Any ideas gratefully received |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through every other and 3rd cell in range
Hi,
Maybe this Sub sonic() Dim SubSum As Long Dim Lastrow2 As Long, y As Long y = 1 Set FirstSht = Sheets("Sheet1") Set SecondSht = Sheets("Sheet2") Lastrow2 = SecondSht.Cells(Cells.Rows.Count, "B").End(xlUp).Row For x = 1 To Lastrow2 Step 3 SubSum = SubSum + SecondSht.Cells(x, 2) - FirstSht.Cells(y, 1) y = y + 2 Next FirstSht.Cells(1, 3) = SubSum End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Atishoo" wrote: HI all I need to create a loop that will subtract the value in every other cell in one range (lets say sheet1 a1 to a100) from the value in every third cell in another range (lets say sheet2 b1 to b150). the output should be the sum of all the subtractions given in a cell lets say sheet1 c1. Any ideas gratefully received |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop through every other and 3rd cell in range
You can do that with normal worksheet formulas if you want, just put this
formula in C1 (on any sheet you want to)... =SUMPRODUCT((MOD(ROW(Sheet2!B1:B150),3)=1)*Sheet2! B1:B150)-SUMPRODUCT((MOD(ROW(Sheet1!A1:A100),2)=1)*Sheet1!A 1:A100) Note that the "=1" parts are used if you want to start counting at odd rows (so change them to "=0" if you ranges start at Row 2 instead of Row 1). -- Rick (MVP - Excel) "Atishoo" wrote in message ... HI all I need to create a loop that will subtract the value in every other cell in one range (lets say sheet1 a1 to a100) from the value in every third cell in another range (lets say sheet2 b1 to b150). the output should be the sum of all the subtractions given in a cell lets say sheet1 c1. Any ideas gratefully received |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop range change a cell color | Excel Programming | |||
Loop through last cell/row of used range | Excel Discussion (Misc queries) | |||
loop code on cell range | Excel Discussion (Misc queries) | |||
Loop through range and add each cell contents to a string | Excel Programming | |||
loop through cells in a range and pick up corresponding cell values in another range | Excel Programming |