Find the times of day max number occurred

[joh1135]

I need help calculating the times of the day that the maxinum number occurred in each column and have it displayed in row 42

Thanks

Book1
	A	B	C	D	E	F
1	Time	A	B	C	D	E
2	6:00	4	2	6	2	1
3	6:30	6	1	-	2	-
4	7:00	8	3	5	0	3
5	7:30	8	4	-	-	-
6	8:00	7	6	7	2	9
7	8:30	3	2	-	-	6
8	9:00	4	2	3	0	6
9	9:30	3	2	-	-	6
10	10:00	3	4	2	0	5
11	10:30	2	2	-	-	13
12	11:00	3	1	2	0	16
13	11:30	6	4	-	-	4
14	12:00	8	2	2	0	0
15	12:30	6	2	2	-	-
16	13:00	8	3	2	4	0
17	13:30	1	6	3	-	2
18	14:00	4	3	2	21	0
19	14:30	9	3	2	14	16
20	15:00	8	3	2	0	12
21	15:30	0	3	2	13	13
22	16:00	0	1	4	11	12
23	16:30	0	4	2	14	12
24	17:00	0	4	4	3	16
25	17:30	8	5	7	-	13
26	18:00	2	4	1	0	13
27	18:30	0	1	2	-	10
28	19:00	1	0	2	0	12
29	19:30	-	1	1	-	-
30	20:00	0	0	1	0	0
31	20:30	-	-	-	-	-
32	21:00	0	0	1	0	1
33	21:30	-	-	-	-	-
34	22:00	-	0	-	-	-
35	22:30	-	-	-	-	-
36	23:00	-	-	-	-	-
37	23:30	-	-	-	-	-
38	Avg	3.9	2.5	2.8	4.1	7.4
39
40
41	MAX	9	6	7	21	16
42	WHEN	14:30	8:0013:30	8:0017:00	14:00	11:0014:3017:00
Sheet1

 

[jindon]

Again?
Paste the code onto Module
Use in cell
B42
=MaxIfs($A$2:B37,Column())
and copy to the right

Function MaxIfs(rng As Range, ref As Long)
Dim r As Range, ff As String, myMax
myMax = WorksheetFunction.Max(rng.Columns(ref))
Set r = rng.Columns(ref).Find(myMax,,,xlWhole)
ff = r.Address
Do
     MaxIfs = MaxIfs & r.Offset(,-ref+1).Text & Chr(32)
     Set r = rng.Columns(ref).FindNext(r)
Loop Until ff = r.Address
MaxIfs = Trim(MaxIfs)
End Function

 

[joh1135]

I get the following error

User-define type not defined

Function MaxIfs(rng As Range, ref As Long)
Dim r As Range, ff As Sting, myMax

 

[jindon]

Should read as String, previous code edited.

 

[joh1135]

Jindon

That fixed that line - now I get a debug error at

ff r.Address

Expected Sub, Function or Property



Thanks again for you help.....

 

[jindon]

Ah, typo

should read as

ff = r.Address

 

[joh1135]

jindon -

Thanks for the help - that is what I thought it was and after changing it - I no longer get an error.

But, I am now getting #VALUE! in the cells that have the formula

=MaxIfs($A$2:B37,COLUMN()) in them

any ideas????

Appreciate your time and help with this....

 

[jindon]

Hi
How about this one?

Function MaxIfs(rng As Range, ref As Long)
Dim r As Range, ff As String, myMax
myMax = WorksheetFunction.Max(Range(rng.Columns(ref).Address))
Set r = Range(rng.Columns(ref).Address).Find(myMax,,,xlWhole)
ff = r.Address
Do
     MaxIfs = MaxIfs & r.Offset(,-ref+1).Text & Chr(32)
     Set r = Range(rng.Columns(ref).Address).FindNext(r)
Loop Until ff = r.Address
MaxIfs = Trim(MaxIfs)
End Function

 

[joh1135]

Same results -

I put msgbox Maxifs after the line

MaxIfs = MaxIfs & r.Offset(, -ref + 1).Text & Chr(32)

and got the following results 12:00, 17:00,06:30,06:30,05:00,11:00,17:00,14:00
06:00,14:30,07:30,08:00,07:30,08:00,14:30

I put the msgbox after the following and the same results:

Set r = Range(rng.Columns(ref).Address).FindNext(r)

and the same thing before the end of the function

Thanks

 

[jindon]

Dave

Strange...

So the formula is entered in cell B42 and copy to the right, correct?

If so, can you just add msgbox to check myMax please?