Function for Finding Max in an a range and outputting the name of cell VBA

[bmuel13]

Hello, I am stuck on creating code to find the max in a set of data and outputting the name of the cell. For Example if I have the following data set:
[TABLE="width: 194"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]VIVIX[/TD]
[TD="align: right"]0.142966875[/TD]
[/TR]
[TR]
[TD]HLIEX[/TD]
[TD="align: right"]0.104074291[/TD]
[/TR]
[TR]
[TD]USNQX[/TD]
[TD="align: right"]0.144564163[/TD]
[/TR]
[TR]
[TD]HNASX[/TD]
[TD="align: right"]0.072884645[/TD]
[/TR]
[TR]
[TD]SCVIX[/TD]
[TD="align: right"]0.068055342[/TD]
[/TR]
[TR]
[TD]MMYLX[/TD]
[TD="align: right"]0.056515137[/TD]
[/TR]
[TR]
[TD]JDSCX[/TD]
[TD="align: right"]0.080380966[/TD]
[/TR]
[TR]
[TD]WAMCX[/TD]
[TD="align: right"]0.03907045[/TD]
[/TR]
</tbody>[/TABLE]

The funtion needs evaluate the entire data set and output the name next to the max so in this case USNQX

Thank you for your help.

 

[JLGWhiz]

Re: Function for Finding Max in an a range and outputting the name of cell VBA PLEASE HELP

Here is one way.

Sub t()
Dim fn As Range
Set fn = Range("B:B").Find(Application.Max(Range("B:B")), , xlValues)
        If Not fn Is Nothing Then
            MsgBox fn.Offset(, -1).Value
        End If
End Sub

and as a function (UDF)

Function getNameInCell(rng As Range) As String
Dim fn As Range
Set fn = rng.Find(Application.Max(rng), , xlValues)
        If Not fn Is Nothing Then
            getNameInCell = fn.Offset(, -1).Value
        End If
End Function

 

[JLGWhiz]

Re: Function for Finding Max in an a range and outputting the name of cell VBA PLEASE HELP

The function needs work. I am on it now.

don't know what happened, i go a bad result but now it works.

Function getNameInCell(rng As Range) As String
Dim fn As Range
Set fn = rng.Find(Application.Max(rng), , xlValues)
        If Not fn Is Nothing Then
            getNameInCell = fn.Offset(, -1).Value
        End If
End Function

 

[bmuel13]

Re: Function for Finding Max in an a range and outputting the name of cell VBA PLEASE HELP

hey JLG , I appreciate your help. I'm still having some trouble with the function. when i try to run it, it returns a blank. Not an error but simply dissapears when I press enter. Thanks!

 

[JLGWhiz]

Re: Function for Finding Max in an a range and outputting the name of cell VBA PLEASE HELP

hey JLG , I appreciate your help. I'm still having some trouble with the function. when i try to run it, it returns a blank. Not an error but simply dissapears when I press enter. Thanks!

The function should be put into the standard code module1 and then used like a worksheet function.

=getNameInCell(B:B)

It only returns the cell value for the cell to the immediate left of the found MAX value. The range entered in parentheses should be the range containing the values to check for Max value. You can shorten the range by inclusing the row numbers, eg. (B2:B20)

It works for me using your data in Post #1 .

 

[JLGWhiz]

Re: Function for Finding Max in an a range and outputting the name of cell VBA PLEASE HELP

hey JLG , I appreciate your help. I'm still having some trouble with the function. when i try to run it, it returns a blank. Not an error but simply dissapears when I press enter. Thanks!

I also thought of the possibility that the data might not be in adjacent columns. If that is the case, then the function fails. The columns must be contguous and the search column must be right of the target column.