excel 93 macros index match to display time of MAX result

[venturer2012]

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

 

[theBardd]

Maybe try

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

 

[venturer2012]

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.

 

[theBardd]

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

Can you post an example workbook somewhere?

 

[theBardd]

I had a play, see if this is better

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

 

[venturer2012]

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?

 

[theBardd]

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

 

[theBardd]

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

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

 

[venturer2012]

You can do that without select

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.

 

[venturer2012]

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