Extracting column number of max value

[Guest]

I have the following VBA code to get "max value" and "the column number"
where max value is. I can get the max value, but not the column number. Any
suggestions?

Thanks.

Sub model()

For i = 1 To 16
Set eachrow = Range("data").Rows(i)
maxvalue = Application.WorksheetFunction.Max(eachrow)
colnumber = maxvalue.Column

Cells(i, 10) = maxvalue
Cells(i, 11) = colnumber

Next
End Sub

 

[Bernie Deitrick]

Green,

Sub test2()
For i = 1 To 16
Set eachrow = Range("data").Rows(i)
Cells(i, 10) = Application.WorksheetFunction.Max(eachrow)
Cells(i, 11) = Application.Match(Cells(i, 10).Value, eachrow, False)
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

I should note that the column number will be relative within the range
"data", which is fine if data starts in column 1. Other wise, you could
either change
Cells(i, 11) = Application.Match(Cells(i, 10).Value, eachrow, False)
to
Cells(i, 11) = Application.Match(Cells(i, 10).Value, eachrow.EntireRow,
False)

or add the number (less one) of the first column of data.

HTH,
Bernie

 

[Guest]

MATCH function. This is great, Bernie.

Thanks!