Time intervals in Excel VBA Macro

[tingtingli]

Hello all,

I have a worksheet that contains numerous data for a particular cell site at each hour of a day and the hours are listed in military time.

I am currently trying to develop a macro that will ask the user for a time interval (start time and end time) that they wish to get the data from and my macro would add up the data for that specified time interval and output it to the user.

I have 2 problems.

1. Once I get the time interval from the user, I don't know
how to increment the time from start to end and have
my macro read in only the data for that time interval.

2. When I run the loop to see the time, the macro is not
seeing the time from the excel sheet. I tried to do
while time = "1:00:00 AM" and I tried using do while
time = "0.0416666666666667" (which is the numerical
representation of 1am) and in both cases in won't enter
my loop even though I am in the row of the data for 1am.

I would really appreciate it if someone can help me out with this.

Thank you in advance,

Tingtingli

 

[SkipVought]

Hi ting,

First, you probably did not add the integer portion of the date/time value.

Now here's a quick and dirty way I used to find a start date row...

Sub FindStart()
    Dim dStart As Date, dEnd As Date, ans, tim, t1
    t1 = 24
    ans = InputBox("enter start time")
    
    tim = Split(ans, ":")
    For i = LBound(tim, 1) To UBound(tim, 1)
        dStart = dStart + tim(i) / t1
        t1 = t1 * 60
    Next
    dStart = Int(Now) + dStart
    For Each t In Range("Time")
        If dStart <= t.Value Then
            MsgBox t.Value
            StartRow = t.Row
            Exit For
        End If
    Next
End Sub

Naturally, you would have to do more error checking for the time value input and also find the ending time.

Hope this helps ;-) Skip,
metzgsk@voughtaircraft.com