Filling in dates in excel 2000

[westma]

Greetings,
I have some data which contains time stamps with the format m/d/yy h:m0, data every ten minutes. In cells B7 and B8 the user enters start and stop times respectively. From this I can calculate the row which would contain the stop time, displayed in D8. I have also calculated the number needed to add to each date to create 10-min intervals, displayed in cell D4. I have done a formula in EVERY cell in column A to fill in the times between start and stop, and a 0 is displayed after the final stamp. This, however, makes calculating the worksheet very long, and makes the file much bigger. I need a macro that does something like this (starting with cell A14, A13 = start time):

If A13<B8, display A13+D4, move to next cell and test.
else end

I am not experienced enough with VBA to write this code. Thanks for your help

 

[xlbo]

Why not try recording something / looking in the VBA help files / searching the forum and asking about a specific problem rather than getting someone to write an entire piece of code for you...

As an example, this should show you how to loop using 10 minute intervals

Const incr = (1 / 144)'this is the value of 10 minutes 1/((24*60)*10)
stTime = Range("B7").Value
endTime = Range("B8").Value

newTime = stTime + incr

Do While newTime < endTime
    MsgBox Format(newTime, "hh:mm:ss")
    newTime = newTime + incr
Loop

You should be able to insert some code where the message box is to write your formulae out - you can record that part using Tools>Macro>Record New Macro

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[westma]

Geoff,
Sorry, recording isn't working for me. I don't want a message box to display anything or to write any formula out. What I am really missing is the syntax to loop through rows from A14 and display newTime in each cell until the cell value=endTime. Thought this was a specific problem...your replies are definitely appreciated, thank you for your patience.

 

[xlbo]

The code I have given you shows you how to loop in 10 minute intervals - this should mean that you do not need to have formulae all the way down. The message box is just there as an example - you need to figure out what needs to happen at these 10 minute intervals - if you just need to write the time out, then you just need to have an incrementing number in the loop and use that as a row reference.
eg

Const incr = (1 / 144)'this is the value of 10 minutes 1/((24*60)*10)
stTime = Range("B7").Value
endTime = Range("B8").Value
RowToUse = 1
newTime = stTime + incr

Do While newTime < endTime
    'writing to the cell goes here
    newTime = newTime + incr
    RowToUse = RowToUse + 1
Loop

Now, I have given you virtually complete code (which I hate doing) - the whole point of these fora is that people LEARN - if I just give you complete code, what do you learn ??? absolutely nothing. We are here to help you learn, not to do your job for you. Look in the help files at the RANGE object - see what properties it has. Look at the CELLS syntax regarding it's usage with the RANGE object. You should be able to figure out how to write to a cell using row and column numbers....


Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[westma]

Thank you for your help. Do you have any suggestions on maybe a faster way to do this, or make the macro run faster. Two months worth of data takes about 40 seconds for the macro to finish, and two months is not very long.

 

[xlbo]

Well - how many rows are you filling ??
For starters - take a look at Application.screenupdating and application.calculation properties

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question