+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

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

    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!
    Attached Files Attached Files
    Last edited by ExcelQuestFL; 02-25-2010 at 02:51 PM.

  2. #2
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

    Re: Macro Loop – Average Value tagged to Time Range

    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.

  3. #3
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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?
    Last edited by StephenR; 02-24-2010 at 08:07 AM.

  4. #4
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

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

    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.

  5. #5
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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.

  6. #6
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

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

    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.
    Last edited by ExcelQuestFL; 02-24-2010 at 01:04 PM.

  7. #7
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

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

    It looks like I needed to change the code to

    Please Login or Register  to view this content.

    Will be checking the accuracy now.

  8. #8
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

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

    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.

  9. #9
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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.

  10. #10
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

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

    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.
    Last edited by ExcelQuestFL; 02-25-2010 at 02:08 PM.

  11. #11
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

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

    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

  12. #12
    Registered User
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    89

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

    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!!!!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1