excel 93 macros index match to display time of MAX result

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: excel 93 macros index match to display time of MAX result

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile excel 93 macros index match to display time of MAX result

     
    sunny boy data. I have a macro to show maximum daily output but I am failing with the time of that max output. Here is the debugging dialogue:
    Sub maxtime()
    '
    ' maxtime Macro
    ' Macro recorded 23/04/2012 by mcd
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Range("D154").Select
    ActiveCell.FormulaR1C1 = "=MAX(R[-134]C[-1]:R[-34]C[-1])"
    ActiveCell=INDEX($A10:$A120,MATCH(MAX($B10,$B20,B120),$B10:$B120,0)-2)
    Selection.NumberFormat = "[$-409]h:mm:ss AM/PM;@"
    End Sub


    It is the INDEX line which starts the debugger.

    Any suggestions welcome

  2. #2
    Board Regular
    Join Date
    Jan 2012
    Posts
    751
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    Maybe try

    Code:
    theTime = Activesheet.Evaluate("INDEX($A10:$A120,MATCH(MAX($B10,$B20,B120),$B10:$B120,0)-2)")

  3. #3
    New Member
    Join Date
    Apr 2012
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    Thanks for your swift reply. TheBardd, I think your suggestion is useful but it always returns a time of 12:00:00 and the cell formula is: =MAX(C20:C120).
    The syntax is good because the macro does not bring up the debugger.

    In my sheet the time column is A and the kWh column is B,
    I will return later to see if you have any more ideas.

  4. #4
    Board Regular
    Join Date
    Jan 2012
    Posts
    751
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    That sounds like it is returning 0, formatted as time.

    Can you post an example workbook somewhere?

  5. #5
    Board Regular
    Join Date
    Jan 2012
    Posts
    751
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    I had a play, see if this is better

    Code:
    Sub maxtime()
    Dim myTime As Date
        With ActiveCell
        
            .Formula = "=MAX(B10:B120)"
            .NumberFormat = "general"
            .Offset(0, 1).Formula = "=INDEX($A10:$A120,MATCH(MAX($B10:B120),$B10:$B120,0))"
            .Offset(0, 1).NumberFormat = "[$-409]h:mm:ss AM/PM;@"
        End With
    End Sub

  6. #6
    New Member
    Join Date
    Apr 2012
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    Thank you TheBardd. I have modified it slightly, adding the line
    " Range("C155").Select " so now the total for the day, max o/p and time are displayed at the bottom of the sheet.

    I could not have done this without your help.

    It is a bit late for me but why do not they teach children (my son is 15) this sort of thing in "IT" at school?

  7. #7
    Board Regular
    Join Date
    Jan 2012
    Posts
    751
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    I don't think kids at school want to learn this stuff. Excel is not cool, phone apps is what they are interested in.

  8. #8
    Board Regular
    Join Date
    Jan 2012
    Posts
    751
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    Quote Originally Posted by venturer2012 View Post
    Thank you TheBardd. I have modified it slightly, adding the line
    " Range("C155").Select " so now the total for the day, max o/p and time are displayed at the bottom of the sheet.
    You can do that without select

    Code:
    Sub maxtime()
    Dim myTime As Date
        With Range("C155")
        
            .Formula = "=MAX(B10:B120)"
            .NumberFormat = "general"
            .Offset(0, 1).Formula = "=INDEX($A10:$A120,MATCH(MAX($B10:B120),$B10:$B120,0))"
            .Offset(0, 1).NumberFormat = "[$-409]h:mm:ss AM/PM;@"
        End With
    End Sub

  9. #9
    New Member
    Join Date
    Apr 2012
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

    Quote Originally Posted by theBardd View Post
    You can do that without select

    Code:
    Sub maxtime()
    Dim myTime As Date
        With Range("C155")
        
            .Formula = "=MAX(B10:B120)"
            .NumberFormat = "general"
            .Offset(0, 1).Formula = "=INDEX($A10:$A120,MATCH(MAX($B10:B120),$B10:$B120,0))"
            .Offset(0, 1).NumberFormat = "[$-409]h:mm:ss AM/PM;@"
        End With
    End Sub
    Are you still out there MrBardd?
    I have been using this macro with great success until the last few days. This unfortunately coincided with my Sunny Beam failing. SMA very kindly sent out a replacement unit which is fine but the macro was returning a value of "0" on some data files. I have now concluded this is nothing to do with the replacement Sunny Beam but due to the appalling weather! The MAX function seems to return a value of zero if all the values are less than one - eg 0.234 (watt hours) .
    Is this just me or normal for Office 2003 Excel.

  10. #10
    New Member
    Join Date
    Apr 2012
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel 93 macros index match to display time of MAX result

      
    I still have the problem outlined in my previous post. Is the anyone out there who can help with this?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com