Page 1 of 2 12 LastLast
Results 1 to 10 of 16

excel 93 macros index match to display time of MAX result

This is a discussion on excel 93 macros index match to display time of MAX result within the Excel Questions forums, part of the Question Forums category; sunny boy data. I have a macro to show maximum daily output but I am failing with the time of ...

  1. #1
    New Member
    Join Date
    Apr 2012
    Posts
    9

    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
    492

    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

    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
    492

    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
    492

    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

    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
    492

    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
    492

    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

    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

    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?

Page 1 of 2 12 LastLast

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