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

 

[steve the fish]

Be easier to use subtotal function:

returnVal(0) = Application.WorksheetFunction.Subtotal(4, .Range("A1:A" & krow))

 

[Jimmy509]

Thanks. Would that allow me to use .Address at the of it?
i can get the max of the column with no issue. The problem the name of associated to it in column B
for example if the max value in column A is in A3. I want to get the address and offset it by 1 using .Address.Offset(0,1) to shift to B3 and get the value in B3.

 

[steve the fish]

Just replace your worksheetfunction.max in the code with the subtotal and it should do what you ask. You cant use slanted “ they need to be straight " so you need to replace them in the Set rg line.

 

[Jimmy509]

Thanks for your reply. Although it’s works for the returnVal(0). It does not for rg. It’s still coming up empty.

 

[mikerickson]

Wouldn't VLOOKUP do what you want

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

 

[steve the fish]

Wouldn't VLOOKUP do what you want

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

Not for visible cells mike.

 

[Rick Rothstein]

@steve the fish,

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

 

[steve the fish]

@steve the fish,

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

Hi Rick. From the code in the opening post I presumed he was using a filter:

 returnVal(0) = Application.WorksheerFunction.Max (.Range(“A1:A” & krow).SpecialCells(xlTypeVisible))

 

[Rick Rothstein]

Sorry, I completely missed the SpecialCells(xlTypeVisible)