Return a row with the Min or Max funtion

[Guest]

Is there a way to return the column where the min or max exists rather than
returning the min or max? I am using WorksheetFunction.Min. Thank you very
much.

 

[Bob Phillips]

=MATCH(MAX(1:1),1:1,0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Tom Ogilvy]

Assume rng is a reference to a single row

Sub FindMinColumn()
Dim rng As Range, rng1 As Range, ans As Variant
Set rng = Rows(3).Cells
ans = Application.Match(Application.Min(rng), rng, 0)
If Not IsError(res) Then
Set rng1 = rng(1, ans)
MsgBox rng1.Column
Else
MsgBox "Problems"
End If

End Sub