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.

Please Login or Register  to view this content.

[ExcelQuestFL]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Will be checking the accuracy now.

[ExcelQuestFL]

I double checked the values from the Sample and they are correct. Strangely the Macro finds the correct results for the 1st and 4th day. The other 3 are off slightly. I looked at the code and not sure why it's not pulling the correct values. The offsets seem correct to me, although I'm no Excel pro. I appreciate the work you've done on it, and it seems like we're close. Just need to get the other 3 values right.

[StephenR]

I made a silly mistake - the average was only averaging the start and end values and not the bit in between.

Please Login or Register  to view this content.

[ExcelQuestFL]

Are your values matching up with the sample? Even if I manually input the average values, it's still not coming out properly.

I see where you input Range into the code so that makes sense, and I think the offsets look correct too, but the values are off. The 1st and 3rd values remain correct, but the others are still off.

EDIT: Not sure what I was doing before, but now I'm seeing the correct values! Let me run through all the sheets and make sure.

[StephenR]

If I put 0.45 I get these results.

1999.10.04 1.468869
1999.10.05 1.471685
1999.10.06 1.468646
1999.10.07 1.471054
1999.10.08 1.471300

[ExcelQuestFL]

Stephen....THANK YOU!!! Outstanding results and the code is concise.

Over the weekend I'll speak to one of my buddies to help me understand everything in it (currently I understand bits and pieces but he'll be able to explain it in its entirety.) I really appreciate your effort in completing this and the help from this forum! Thanks!!!!!!!