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!