Search code examples
vbaexcelworksheet-function

VBA - Get String Associated with Max Value in Range

I am in the process of writing a macro in excel and need to get the string or text associated with the max value in a range.

My range looks something like this:

A | B
CR1 | 2.33
CR2 | 5.1
CR3 | 10.0
CR4 | 3.8

I was able to find the MAX value in column B, but now I need the associated string in column A. So in this case, given B3 (10.0) is the max value, I want to pull out 'CR3'.

So my code for pulling the max value is:

 Set myRange = Application.InputBox( _
 prompt:="Please select the Range.", Title:="Graph Range", Type:=8)
 highestNum = Application.WorksheetFunction.Max(myRange)

What can I do to get the associated string in column A? I've tried using .Address, but that hasn't gotten me anywhere.

Thanks in advance!



!!!!!!! Edit/Update - Solved: !!!!!!!

As another contributer suggested, I needed to use inded + Match. See below for Solution.

Set myRange = Application.InputBox( _
prompt:="Please select the Primary KPI 'Lift' Data to Graph.", Title:="Graph Range", Type:=8)

Set rngColumn2 = myRange.Areas(2)

highestNum = Application.WorksheetFunction.Max(myRange)

test = Application.WorksheetFunction.Index(myRange, Application.WorksheetFunction.Match(highestNum, rngColumn2, 1), 1)
Solution

  • You just need an index(Match,Match) function. It would take a look at the highest number in B and return the A.