I have uploaded my workeet at the following link
http://www25.brinkster.com/shreejipc...Difference.htm


What I want to do is, check the difference between the two dates AW2-AV2 and
keep it in memory, then check difference of AX2-AW2, keep it in memory... do
this till the last date in the same row and whichever number is highest in
the memory, put in it cell AS2. Continue the same thing till the last row.

Note: there is no fixed row or column for the dates, the dates can go up to
the last column IV and the last row 65536.

I have a date in cell B1 (which is a dynamic date and I will keep on
changing the date as per my requirements). I want to check the difference
between the date in cell B1 and the last date in the first row and put it in
cell AT2. Then check the difference between the date in cell B1 and the last
date in the second row and put it in cell AT2... do this till the last row.

Maxi

Try this:

Sub DateDifferences()

Dim v(256) As Variant
Dim iLastRow As Long, iLastCol As Integer
Dim r As Long, c As Integer, i As Integer

iLastRow = Cells(Rows.Count, "AV").End(xlUp).Row

For r = 2 To iLastRow

iLastCol = Cells(r, Columns.Count).End(xlToLeft).Column ' Find Last Column

i = 0
For c = Cells(r, "AW").Column To iLastCol ' Calculate Date Differences
v(i) = Cells(r, c).Value - Cells(r, c - 1).Value
i = i + 1
Next c

Cells(r, "AS") = Application.Max(v) ' Get Maximum
Cells(r, "AT") = Cells(r, iLastCol) - Range("B1") ' Last Date - B1

Next r

End Sub


"mac_see" wrote:

I have uploaded my workeet at the following link
http://www25.brinkster.com/shreejipc...Difference.htm


What I want to do is, check the difference between the two dates AW2-AV2 and
keep it in memory, then check difference of AX2-AW2, keep it in memory... do
this till the last date in the same row and whichever number is highest in
the memory, put in it cell AS2. Continue the same thing till the last row.

Note: there is no fixed row or column for the dates, the dates can go up to
the last column IV and the last row 65536.

I have a date in cell B1 (which is a dynamic date and I will keep on
changing the date as per my requirements). I want to check the difference
between the date in cell B1 and the last date in the first row and put it in
cell AT2. Then check the difference between the date in cell B1 and the last
date in the second row and put it in cell AT2... do this till the last row.

Maxi

That worked !!! Thank you so much !

"Toppers" wrote:

Try this:

Sub DateDifferences()

Dim v(256) As Variant
Dim iLastRow As Long, iLastCol As Integer
Dim r As Long, c As Integer, i As Integer

iLastRow = Cells(Rows.Count, "AV").End(xlUp).Row

For r = 2 To iLastRow

iLastCol = Cells(r, Columns.Count).End(xlToLeft).Column ' Find Last Column

i = 0
For c = Cells(r, "AW").Column To iLastCol ' Calculate Date Differences
v(i) = Cells(r, c).Value - Cells(r, c - 1).Value
i = i + 1
Next c

Cells(r, "AS") = Application.Max(v) ' Get Maximum
Cells(r, "AT") = Cells(r, iLastCol) - Range("B1") ' Last Date - B1

Next r

End Sub


"mac_see" wrote:

I have uploaded my workeet at the following link
http://www25.brinkster.com/shreejipc...Difference.htm


What I want to do is, check the difference between the two dates AW2-AV2 and
keep it in memory, then check difference of AX2-AW2, keep it in memory... do
this till the last date in the same row and whichever number is highest in
the memory, put in it cell AS2. Continue the same thing till the last row.

Note: there is no fixed row or column for the dates, the dates can go up to
the last column IV and the last row 65536.

I have a date in cell B1 (which is a dynamic date and I will keep on
changing the date as per my requirements). I want to check the difference
between the date in cell B1 and the last date in the first row and put it in
cell AT2. Then check the difference between the date in cell B1 and the last
date in the second row and put it in cell AT2... do this till the last row.

Maxi