Simple subtracting every 150 rows

  • Thread starter Thread starter Seth
  • Start date Start date
S

Seth

I just want to subtract two rows every 150 line.
=B300-B150
=B450-B300
=B600-B450
etc.

I am sure this pretty easy to do just can't figure it out.
I have about 20000 lines of data.

Thanks
 
Hi Seth,

Assuming you want this to appear in C300,C450, C600 etc.
Put your formula =B300-B150 in C300
Then Highlight from C1 to C300 and
double click the fill handle.

This will work if there are no blanks in column B.
If you do have blanjs in col B then you will need
to grab the fill handle and drag it down manually.

HTH
Martin
 
modify this idea to suit.

Sub subtractem()
mc = "e"
For i = 10 To Cells(Rows.Count, mc).End(xlUp).Row Step 10
'MsgBox Cells(i, mc).Address
MsgBox Cells(i + 10, mc) - Cells(i, mc)
Next i
End Sub
 
If your data starts in row 10, use a formula like

=INDIRECT("A"&150*(ROW()-ROW(C$10)+1))-INDIRECT("B"&150*(ROW()-ROW(C$10)+1))

and fill down for as many cells as you need to go.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Back
Top