Calculating the difference between cell values in different rows - Loop
 

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

Calculating the difference between cell values in different rows - Loop
 

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



--
Cheers
Nigel



"joecrabtree" wrote in message
oups.com...
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

Calculating the difference between cell values in different rows - Loop
 

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


Nigel wrote:
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



--
Cheers
Nigel



"joecrabtree" wrote in message
oups.com...
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

Calculating the difference between cell values in different rows - Loop
 

Just modify one line to read
Sheets("Sheet1").Cells(opr, 1) = Abs(.Cells(xr + 1, 1) - .Cells(xr, 1))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"joecrabtree" wrote in message
ups.com...
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


Nigel wrote:
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



--
Cheers
Nigel



"joecrabtree" wrote in message
oups.com...
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