excel 93 macros index match to display time of MAX result

venturer2012

New Member
Joined
Apr 24, 2012
Messages
9
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:cool:
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Maybe try

Code:
theTime = Activesheet.Evaluate("INDEX($A10:$A120,MATCH(MAX($B10,$B20,B120),$B10:$B120,0)-2)")
 
Upvote 0
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.
:)
 
Upvote 0
That sounds like it is returning 0, formatted as time.

Can you post an example workbook somewhere?
 
Upvote 0
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
 
Upvote 0
:) 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?:LOL:
 
Upvote 0
I don't think kids at school want to learn this stuff. Excel is not cool, phone apps is what they are interested in.
 
Upvote 0
:) 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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top