Associate the maximum value with another value in the same row but a different column

[iand5]

I currently have the code below to find the maximum value in a column. After finding the maximum value I need to associate that number with another value in the same row but a different column. I want to find the maximum value in the range ("AX6:AX29"). I need to associate that number with a value in column A.

It's currently giving me the wrong value.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#303336][FONT=inherit]Dim r As Long
    Dim t As Long
    Dim rslt As String
    With Worksheets("RESOURCE") ' Change to your sheet
        r = Application.WorksheetFunction.Max(.Range("AX6:AX29"))
        t = Application.Match(r, .Range("A6:A29"))
        rslt = .Cells(t, "A")
        Debug.Print rslt
    End With

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit] [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With
[/FONT][/COLOR]</code>

 

[Scott Huish]

You didn't allow for the offset of starting at row 6:
Although this would just return the same value as r

Sub test()
Dim r As Long
    Dim t As Long
    Dim rslt As String
    With Worksheets("RESOURCE") ' Change to your sheet
        r = Application.WorksheetFunction.Max(.Range("AX6:AX29"))
        t = Application.Match(r, .Range("A6:A29"))
        rslt = .Cells(t + 5, "A")
        Debug.Print rslt
    End With
End Sub