Find the offset value of the max of a column

[Jimmy509]

Hey guys
I am working on this code and my plan is to go into a worksheet, get the max of column A. And if the max is found then go to the next row over and get the name associated with it. I wrote this code and the value of the name is coming up blank and I don’t what I am doing wrong.

Dim returnVal (2), rg, rg2 as variant
Dim krow as long
Dim Ws1 As worksheet
krow = ws1.Cells(Rows.Count,1).End(xlUp).Row
With Ws1
returnVal(0) = Application.WorksheerFunction.Max (.Range(“A1:A” & krow).SpecialCells(xlTypeVisible))
Set rg = .Range(“A1:A” & krow).Find(returnVal(0), , xlValues, xlWhole, , False, False, False)
If Not rg is Nothing Then
rg2 = rg.Offset(0,1).Value
returnVal(1) = rg2
Set rg = Nothing
End if
End with

 

[Jimmy509]

@steve the fish,

Why do you think there are hidden rows? Unless I missed it, I don't see that mentioned anywhere.

This is a snippet From a bigger code that’s doing some filtering.

 

[Jimmy509]

Wouldn't VLOOKUP do what you want

=VLOOKUP(MAX(A:A), A:B, 2, False)

No and I plan to do it in VBA

 

[Rick Rothstein]

@steve the fish,

You should be using 104 as the first argument for your Subtotal function (not 4).


@Jimmy509,

Here is a function that returns the name next to the first maximum value it finds in the specified range... you can pull the single line of code out of the function and embed it in your own code if you wish)...

Function GetMaxName(Rng As Range) As String
  GetMaxName = [VLOOKUP(SUBTOTAL(104,A:A),A:B,2,0)]
End Function

 

[steve the fish]

Rick if its hidden by a filter it isnt counted using 4 is it?

 

[Rick Rothstein]

Hmm, you are right... both 4 and 104 work. So a row hidden by a filter is not a hidden row according to SUBTOTAL then... it is considered something else instead. Interesting.

So, it appears that the 104 can be replaced by 4 in the code I posted in Message #13 if desired.

 

[Jimmy509]

Ye

Sorry, I completely missed the SpecialCells(xlTypeVisible)
[/QUOTE

Sorry, I completely missed the SpecialCells(xlTypeVisible)

yes sorry.