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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>> >
>