Decreasing time by 1 hour

[Chris_AAA]

Hi

I have a coulumn (D) with several times. I need to decrease the time by one hour - due to clocks changing.

I have added two columns E&F

In E, I have added 01:00:00
In F > D-E

My issue, is because E is a blank column, when I want to fill down the time it does not recognise the last known cell. I understand what I need to do in esscence. I Need to fill down in relation to a column it recognises has a last known field... say column D with the times for example.

So my question is, (and i have searched the net for examples) how do I fill down - but locking the time at 01:00:00 when I try ...


Selection.AutoFill Destination:=Range("e6:e" & Cells(Rows.Count, "d").End(xlUp).Row)

It obviously adds a sequential gain of 1 hour to each row down.

I want to lock the 1 hour time, and fill down in relation to column D to last know row.

Im sure its really easy, but cant get past this puzzle.

Any advise is appreciated.

Thank you kindly.

[jonh]

Range("e6", Range("d6").End(xlDown).Offset(0, 1)).Formula = #1:00:00 AM#

[Chris_AAA]

oh, so the # lock the value from going up.....

Perfect.

Im keen to understand the code - and the part I do not understand is why you have used Ofset, why is this needed?

Im a newbie to vba :-)

[jonh]

Nope. The hashes denote time. I haven't used autofill, I set the range values to 1hr explicitly.

Since the second range is proper to column D, I used offset to move it over to column E.


If every date needs to be changed by the same value you could just set another cell say A1 to 01:00:00, then

[E6] =D6-$A$1

$'s fix the column/row, so using autofill doesn't change that cell, it will always stay @ A1 and if you ever need to remove the time difference you only need to set A1 = 00:00:00.