Macro Loop – Find Specific Times, Extract Date, Average Values of Times

[ExcelQuestFL]

Seeking assistance for a quick macro please.

In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.

Here’s a quick description:

Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible

1:45:

If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.

At this point, the date from Column A next to 22:45 is copied to the Sheet labeled “Output” beginning in A2 if there isn’t already data on this sheet. If there is data, the date should be placed in the first available row of Column A below the data.

Then back to the sheet I was in, I need to Sum the cells from Column F that are on the same row as 22:45 through 1:45 and then divide by how many cells are in the column. Typically its 13, but it does change, so the Count function will need to be used for the F Column range. This average value is then output next to the date in Column A that was copied.
Then move back to the sheet I was running the query and continue this process until the last 1:45 in Column B.

0:45:

If I input 0:45, then I’d like the macro to find the first 0:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 0:45 is located, then the macro moves up to find 21:45 in Column B and the value in the same row in Column F will be used in the calculation.

At this point, the date from Column A next to 0:45 is copied to the Sheet labeled “Output” beginning in A2 if there isn’t already data on this sheet. If there is data, the data should be placed in the first available row of Column A below the data.

Then back to the sheet I was in, I need to Sum the cells from Column F that are on same row as 21:45 through 0:45, and then divide by how many cells are in the column. Typically its 13, but it does change, so the Count function will need to be used for the F Column range. The average value is then output next to the date in Column A that was copied.
Then move back to the sheet I was running the query and continue this process until the last 0:45 in Column B.

***I’ve attached a sample showing the Output values for the first 5 values for each sheet with inputs 0:45 for Sheet 1, 1:45 for Sheet 2, 0:45 for Sheet 3, 1:45 for Sheet 4, 0:45 for Sheet 5, and 1:45 for Sheet 6. I’ve put in Cell H1 a note for the sheet which displays whether the macro would be running 0:45 or 1:45 in case there’s any confusion about the results in the Output sheet.

Let me know if there’s any questions and I’ll do my best to clarify. Thanks and I really appreciate the help!

[ExcelQuestFL]

I hope the description of the macro was not too verbose but I tried to encompass how I see the macro moving through the sheets. I know for you Excel guru's this is a walk in the park. I would definitely appreciate some help on it please.

If there's any confusion about the macro please let me know.

[StephenR]

Is it only ever going to be 0.45 or 1.45 or is that just an illustration?

I don't think it's very difficult to do, but I have never really got to grips with dates and times in Excel and that is what is stumping me at the moment.

EDIT: ok, think I have the time problem sorted, but clarification of above would be welcome. Also, should the code search through all the sheets for these values?

[ExcelQuestFL]

Firstly, thanks for taking a look!

I'll only be searching one sheet at a time for either 0:45 or 1:45. I was thinking I could always input ".03125" for 0:45 or ".072917" for 1:45 as that's the value Excel assigns to the time, but obviously inputting the 0:45 or 1:45 is easier to remember. I'm not picky as long as the results work, I'm happy.

[StephenR]

Try this. It does something, but not sure if it's correct as some of the figures are different from your example. Please check if error lies in the macro or your example. Also, currently just covers the first sheet.

Sub x()
 
Dim i As Long, rFind As Range, rFind2 As Range, sInput As String, sInput2 As String

sInput = Application.InputBox(prompt:="Time, 1.45 or 0.45?", Type:=2)
If sInput = False Or (sInput <> "1.45" And sInput <> "0.45") Then Exit Sub

With Sheet2.Range("B1", Sheet2.Range("B" & Rows.Count).End(xlUp))
    Set rFind = .Cells(.Rows.Count, 1)
    For i = 1 To WorksheetFunction.CountIf(.Cells, CDate(sInput))
        Set rFind = .Find(What:=CDate(sInput), After:=rFind, LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not rFind Is Nothing Then
            sInput2 = IIf(sInput = "0.45", "21.45", "22.45")
            Set rFind2 = .Find(What:=CDate(sInput2), After:=rFind, LookIn:=xlFormulas, _
                          LookAt:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
            If Not rFind2 Is Nothing Then
                Sheet1.Cells(Rows.Count, 1).End(xlUp)(2) = rFind2.Offset(, -1)
                Sheet1.Cells(Rows.Count, 2).End(xlUp)(2) = WorksheetFunction.Average(rFind2.Offset(, 4), rFind.Offset(, 4))
            End If
        End If
    Next i
End With
        
End Sub

[ExcelQuestFL]

Hmmm. On the last couple lines of code I'm getting a run-time error 424: object required for:

Sheet1.Cells(Rows.Count, 1).End(xlUp)(2) = rFind2.Offset(, -1)

Any ideas? I copied and pasted entire code twice and tried running it but Excel and I seem to be at odds this morning.

[ExcelQuestFL]

It looks like I needed to change the code to

Sheet8.Cells(Rows.Count, 1).End(xlUp)(2) = rFind2.Offset(, -1)

Will be checking the accuracy now.