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

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

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