+ Reply to Thread
Results 1 to 12 of 12

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

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    106

    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
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    106

    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,606

    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
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    106

    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,606

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

  6. #6
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    106

    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:

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

  7. #7
    Forum Contributor
    Join Date
    06-03-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007 / Excel 2010
    Posts
    106

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

    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.

+ 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