Calculating the difference between cell values in different rows - Loop

  • Thread starter Thread starter joecrabtree
  • Start date Start date
J

joecrabtree

To All,

I have a list of data in column A: Eg:

1
2
3
4
5
5

I wish to calculate the difference between rows, i.e. ( 2-1), (3-2)
etc, and be able to loop this for however many rows of data I have -
which will vary. I then want to output the ranges to a sheet called
sheet1.

Any help would be much appreciated.

Thanks in advance.

Regards

Joseph Crabtree
 
Assuming your range is in sheet2, change below if not, then use this

Sub DiifList()
Dim xlr As Long, xr As Long, opr As Long
Sheets("Sheet1").Columns(1).ClearContents
With Sheets("Sheet2")
xlr = .Cells(.Rows.Count, "A").End(xlUp).Row
opr = 1
For xr = 1 To xlr - 1
Sheets("Sheet1").Cells(opr, 1) = .Cells(xr + 1, 1) - .Cells(xr, 1)
opr = opr + 1
Next
End With
End Sub
 
Hi Nigel,

Thats great thanks. Is there anyway I can get it to just return
positive numbers. I.e if The difference is 5-10 then the answer would
bed 5 not -5?

Thanks

Joseph Crabtree
 
Just modify one line to read
Sheets("Sheet1").Cells(opr, 1) = Abs(.Cells(xr + 1, 1) - .Cells(xr, 1))
best wishes
 
Back
Top