Column Max Location

[stuhba]

I have a large data set with multiple sheets each with 40k+ values. I have written a macro loop to get the maximum for each column and need to get the row where that value occurs. Macro is as follows:

For n = 1 To numyears
Cells(4 + n, 1).Value = n
Cells(4 + n, 3).Value = Worksheets(SheetPtr).Cells(2, n + 2)
Cells(4 + n, 4).Value = WorksheetFunction.Max(Worksheets(SheetPtr).Columns(n + 2))
Next n

 

[dermie_72]

if you use match on the column it should give you the row number it appears in.

 

[stuhba]

Haven't been able to get syntax right to select that range on the worksheet. It doesn't seem to like the fact that I am using a counter in the sheet name.

 

[dermie_72]

try: Application.WorksheetFunction.Match(WorksheetFunction.Max(Worksheets(SheetPtr).Columns(n + 2)), Range("A:A"), 0)

I wasn't sure what range you were looking at, so used Column A, but you can put in any variable you want.

If you put a variable = the above string, then the variable will equal the row number the max value appears in.

 

[stuhba]

Thanks! Got that modified and working.