Select Cell within a range that has a maximum value

I am trying to select a cell in Excel VBA 2007

Example in row 2, cells A through H have some numbers but cell B2 has the highest val开发者_运维知识库ue. is there a formula that I could use to get the address of the cell B2 ?

Based on this, is there a way I could use a variable to select a Range(":") ?

I am a newbie to VBA so any help would be much appreciated.

Thanks

=CELL("address",INDEX(A2:H2,MATCH(MAX(A2:H2),A2:H2,0)))

EDIT.

Sub max_value_address()
Dim i As Long
i = 2
'This example assigns to A1 cell the address of max value in the range a2:h2
Range("a1").Formula = "=CELL(""Address"",INDEX(A" & i & ":H" & i & ",MATCH(MAX(A" & i & ":H" & i & "),A" & i & ":H" & i & ",0)))"
End Sub

EDIT 2. This version is a little bit more concise.

Sub max_value_address()
Dim i As Long
Dim str As String
i = 2
str = "a" & i & ":h" & i 'assign to str a2:h2
Range("a1").Formula = "=CELL(""address"",INDEX(" & str & ",MATCH(MAX(" & str & ")," & str & ",0)))"
End Sub

The below code might help you to reach your goal. Let us know if it's unclear.

Sub GetHigherValueCellAddress()

    Dim oCell As Excel.Range
    Dim oRange As Excel.Range
    Dim vPrevValue As Variant
    Dim sAddress As String

    Set oRange = Sheets(1).Range("A1:C2")

    For Each oCell In oRange

        If oCell.Value > vPrevValue Then

            sAddress = oCell.Address
            vPrevValue = oCell.Value

        End If

    Next oCell

    MsgBox sAddress

End Sub