| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search dates differences
I have nothing in range AS2:AS10
I have some random dates in range AV2:BB:10 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 BB2-BA2 and whichever number is highest in the memory, put in it cell AS2. Continue the same thing till row 10. Also whatever is the difference of the last dates (BB2-BA1), put it in cell AT2..... do this till row 10. Maxi |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search dates differences
The random dates are in the range AV2:IV10 and not AV2:BB10
"mac_see" wrote: I have nothing in range AS2:AS10 I have some random dates in range AV2:BB:10 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 BB2-BA2 and whichever number is highest in the memory, put in it cell AS2. Continue the same thing till row 10. Also whatever is the difference of the last dates (BB2-BA1), put it in cell AT2..... do this till row 10. Maxi |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search dates differences
Hi Mac,
Try this and see if it does what you want: Sub Macro2() Range("AV2").Select For I = 1 To 9 ThisRow = ActiveCell.Row V0 = ActiveCell.Value V1 = ActiveCell.Offset(0, 1).Value V2 = ActiveCell.Offset(0, 2).Value V3 = ActiveCell.Offset(0, 3).Value V4 = ActiveCell.Offset(0, 4).Value V5 = ActiveCell.Offset(0, 5).Value V6 = ActiveCell.Offset(0, 6).Value Val1 = V1 - V0 Val2 = V2 - V1 Val3 = V3 - V2 Val4 = V4 - V3 Val5 = V5 - V4 Val6 = V6 - V5 SaveVal = Val1 If Val2 Val1 Then SaveVal = Val2 If Val3 SaveVal Then SaveVal = Val3 If Val4 SaveVal Then SaveVal = Val4 If Val5 SaveVal Then SaveVal = Val5 If Val6 SaveVal Then SaveVal = Val6 Range("AS" & ThisRow).Value = SaveVal Range("AT" & ThisRow).Value = Val6 ActiveCell.Offset(1, 0).Select Next I End Sub Thanks, "mac_see" wrote: The random dates are in the range AV2:IV10 and not AV2:BB10 "mac_see" wrote: I have nothing in range AS2:AS10 I have some random dates in range AV2:BB:10 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 BB2-BA2 and whichever number is highest in the memory, put in it cell AS2. Continue the same thing till row 10. Also whatever is the difference of the last dates (BB2-BA1), put it in cell AT2..... do this till row 10. Maxi |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search dates differences
It works but it will stop at BB10. if I have dates in more rows lets say
AV2:BB750, the code would be very long. I don't know VBA but can you use something like xlDown so that irrespective of the date range, it will still give you result and the code will be short? "David" wrote: Hi Mac, Try this and see if it does what you want: Sub Macro2() Range("AV2").Select For I = 1 To 9 ThisRow = ActiveCell.Row V0 = ActiveCell.Value V1 = ActiveCell.Offset(0, 1).Value V2 = ActiveCell.Offset(0, 2).Value V3 = ActiveCell.Offset(0, 3).Value V4 = ActiveCell.Offset(0, 4).Value V5 = ActiveCell.Offset(0, 5).Value V6 = ActiveCell.Offset(0, 6).Value Val1 = V1 - V0 Val2 = V2 - V1 Val3 = V3 - V2 Val4 = V4 - V3 Val5 = V5 - V4 Val6 = V6 - V5 SaveVal = Val1 If Val2 Val1 Then SaveVal = Val2 If Val3 SaveVal Then SaveVal = Val3 If Val4 SaveVal Then SaveVal = Val4 If Val5 SaveVal Then SaveVal = Val5 If Val6 SaveVal Then SaveVal = Val6 Range("AS" & ThisRow).Value = SaveVal Range("AT" & ThisRow).Value = Val6 ActiveCell.Offset(1, 0).Select Next I End Sub Thanks, "mac_see" wrote: The random dates are in the range AV2:IV10 and not AV2:BB10 "mac_see" wrote: I have nothing in range AS2:AS10 I have some random dates in range AV2:BB:10 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 BB2-BA2 and whichever number is highest in the memory, put in it cell AS2. Continue the same thing till row 10. Also whatever is the difference of the last dates (BB2-BA1), put it in cell AT2..... do this till row 10. Maxi |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search dates differences
Dates can also be in more columns. Lets say AV2:IV1056
The code should work irrespective of the rows and columns. Maxi "mac_see" wrote: It works but it will stop at BB10. if I have dates in more rows lets say AV2:BB750, the code would be very long. I don't know VBA but can you use something like xlDown so that irrespective of the date range, it will still give you result and the code will be short? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search dates differences
Sub Macro2()
Range("AV2").Select Do Until ActiveCell.Value = "" ThisRow = ActiveCell.Row V0 = ActiveCell.Value V1 = ActiveCell.Offset(0, 1).Value V2 = ActiveCell.Offset(0, 2).Value V3 = ActiveCell.Offset(0, 3).Value V4 = ActiveCell.Offset(0, 4).Value V5 = ActiveCell.Offset(0, 5).Value V6 = ActiveCell.Offset(0, 6).Value Val1 = V1 - V0 Val2 = V2 - V1 Val3 = V3 - V2 Val4 = V4 - V3 Val5 = V5 - V4 Val6 = V6 - V5 SaveVal = Val1 If Val2 Val1 Then SaveVal = Val2 If Val3 SaveVal Then SaveVal = Val3 If Val4 SaveVal Then SaveVal = Val4 If Val5 SaveVal Then SaveVal = Val5 If Val6 SaveVal Then SaveVal = Val6 Range("AS" & ThisRow).Value = SaveVal Range("AT" & ThisRow).Value = Val6 ActiveCell.Offset(1, 0).Select Loop End Sub If the dates are elsewhere, then the relationships of the dates must be spelled out. This will work for what you have shown. "mac_see" wrote: Dates can also be in more columns. Lets say AV2:IV1056 The code should work irrespective of the rows and columns. Maxi "mac_see" wrote: It works but it will stop at BB10. if I have dates in more rows lets say AV2:BB750, the code would be very long. I don't know VBA but can you use something like xlDown so that irrespective of the date range, it will still give you result and the code will be short? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search dates differences
It does work for unlimited rows but not for columns. If I have dates till the
end of the sheet (column IV), it does not give the correct result. I think there is a lot of confusion in this thread. I am starting a new thread for this issue with a clear explaination. Thanx Maxi "David" wrote: Sub Macro2() Range("AV2").Select Do Until ActiveCell.Value = "" ThisRow = ActiveCell.Row V0 = ActiveCell.Value V1 = ActiveCell.Offset(0, 1).Value V2 = ActiveCell.Offset(0, 2).Value V3 = ActiveCell.Offset(0, 3).Value V4 = ActiveCell.Offset(0, 4).Value V5 = ActiveCell.Offset(0, 5).Value V6 = ActiveCell.Offset(0, 6).Value Val1 = V1 - V0 Val2 = V2 - V1 Val3 = V3 - V2 Val4 = V4 - V3 Val5 = V5 - V4 Val6 = V6 - V5 SaveVal = Val1 If Val2 Val1 Then SaveVal = Val2 If Val3 SaveVal Then SaveVal = Val3 If Val4 SaveVal Then SaveVal = Val4 If Val5 SaveVal Then SaveVal = Val5 If Val6 SaveVal Then SaveVal = Val6 Range("AS" & ThisRow).Value = SaveVal Range("AT" & ThisRow).Value = Val6 ActiveCell.Offset(1, 0).Select Loop End Sub If the dates are elsewhere, then the relationships of the dates must be spelled out. This will work for what you have shown. "mac_see" wrote: Dates can also be in more columns. Lets say AV2:IV1056 The code should work irrespective of the rows and columns. Maxi "mac_see" wrote: It works but it will stop at BB10. if I have dates in more rows lets say AV2:BB750, the code would be very long. I don't know VBA but can you use something like xlDown so that irrespective of the date range, it will still give you result and the code will be short? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| adding differences between dates | Excel Discussion (Misc queries) | |||
| Differences between Dates | Excel Worksheet Functions | |||
| Calculating time differences across two (or more) dates | Excel Discussion (Misc queries) | |||
| Calculating differences in dates | Excel Discussion (Misc queries) | |||
| Calculating differences between dates | Excel Worksheet Functions | |||
![mac_see[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
