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



--
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

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

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