Extracting column number of max value
 

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

Extracting column number of max value
 

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


"GreenInIowa" wrote in message
...
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

Extracting column number of max value
 

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"GreenInIowa" wrote in message
...
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

Extracting column number of max value
 

MATCH function. This is great, Bernie.

Thanks!




"Bernie Deitrick" wrote:

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


"GreenInIowa" wrote in message
...
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