CODEWITHSUNDEEP
vbaexcelexcel-2007
zack
zack

Reputation: 7385

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 value. 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

Upvotes: 1

Views: 7287

Answers (2)

Nicola Cossu
Nicola Cossu

Reputation: 56407

=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

Upvotes: 2

Tiago Cardoso
Tiago Cardoso

Reputation: 2107

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

Upvotes: 0

Related Questions