Prev: How do I change a mouse icon !!!!!!!!!!
Next: IP address to Hostname in Excel
From: Atishoo on 11 Apr 2010 04:30
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
From: Mike H on 11 Apr 2010 05:03
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
From: Rick Rothstein on 11 Apr 2010 10:41
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!A1: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" <Atishoo(a)discussions.microsoft.com> wrote in message
news:8D362923-D852-460B-8879-A270B8171646(a)microsoft.com...
> 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

 | 
Pages: 1
Prev: How do I change a mouse icon !!!!!!!!!!
Next: IP address to Hostname in Excel