Loop+ Offset issue

[kamiljaku]

Hey,

what is wrong in the code below. I need to receive a output like

00:00
00:15
00:30
00:45

but instead d I get

00:00
00:15
00:15
00:15


----------------------------------------------------

Dim interval As Range
Range("E6").Value = "00:00"
Set interval = Range("E6")


For i = 1 To 95
interval.Offset(i, 0) = interval.Offset(i, 0) + "00:15"
Next i

 

[oldbrewer]

put 00:15 in say Z1 and make it interval.Offset(i, 0) = interval.Offset(i, 0) + cells(1,26)

 

[Joe4]

Two things:
You are not adding to the previous value, but the current value in your formula.
Add time, not a string. One hour is equal to 1/24. So 15 minutes is equal to 1/96.
This is because date/time is store as integers in Excel, specifically the number of days since 1/0/1900. So one day = 1, and hours are a fraction of one day.

So try this:

Dim interval As Range
Range("E6").Value = "00:00"
Set interval = Range("E6")


For i = 1 To 95
    interval.Offset(i, 0) = [COLOR=#ff0000]interval.Offset(i - 1, 0) + 1 / 96[/COLOR]
Next i

 

[kamiljaku]

Two things:
You are not adding to the previous value, but the current value in your formula.
Add time, not a string. One hour is equal to 1/24. So 15 minutes is equal to 1/96.
This is because date/time is store as integers in Excel, specifically the number of days since 1/0/1900. So one day = 1, and hours are a fraction of one day.

So try this:

Dim interval As Range
Range("E6").Value = "00:00"
Set interval = Range("E6")


For i = 1 To 95
    interval.Offset(i, 0) = [COLOR=#ff0000]interval.Offset(i - 1, 0) + 1 / 96[/COLOR]
Next i

Thank you it worked and I understood the explanation very greatful

 

[Joe4]

You are welcome.
Glad I was able to help!