CODEWITHSUNDEEP
vbaexcelmin
Samuel Martin
Samuel Martin

Reputation: 11

Getting the cell of value of the Cell with Min Value in a Range

I have a table with data similar to below

      A             B       C     D     E      
1                 London    NY    LA  Chicago
2 Population      11        18    16    12

I have the following code to get Max Value: (Sucessfully)

Dim Rng As Range
Dim Ray
Dim Max As Integer
Set Rng = Range(Range("B2"), Range("E2").End(xlUp))
Ray = Rng.Value
Max = Application.Max(Ray)

Then, I´m trying to get the column with the Max Value. My goal is to get "C" or C1 to be able to know that NY (C1) is the one with Max Value.

I´ll try the following but it is not working

For Each cell In Rng
 If cell.Value = Max Then
    MaxAddress = cell.Address
    Exit For
 End If
Next cell

Upvotes: 1

Views: 59

Answers (2)

Jbjstam
Jbjstam

Reputation: 884

Option Explicit

Sub TestMax()
    Dim oSheet As Worksheet
    Dim oRange As Range      
    Dim maxValue As Integer
    Dim oMaxCell As Range

    Set oSheet = ActiveSheet

    Set oRange = oSheet.Range("B2:E2")

    maxValue = Application.Max(oRange.Value)

    For Each oMaxCell In oRange.Cells
        If CInt(oMaxCell.Value) = maxValue Then
            Exit For
        End If
    Next oMaxCell

    Debug.Print oMaxCell.Column

End Sub

Depending on the other data, you might not want to work with integers though

Edit: Oops someone was faster

Upvotes: 0

Vityata
Vityata

Reputation: 43585

This is something with your code, that should work:

Option Explicit

Public Sub TestMe()

    Dim Rng         As Range
    Dim Ray         As Variant
    Dim MaxValue    As Double
    Dim MaxCell     As Range
    Dim cell        As Range

    Set Rng = ActiveSheet.Range(Range("B2"), Range("E2").End(xlUp))
    Ray = Rng.value
    MaxValue = Application.Max(Ray)

    For Each cell In Rng
        If cell = MaxValue Then
            Set MaxCell = cell
            Exit For
        End If
    Next cell

    Debug.Print MaxCell.Address
    Debug.Print MaxCell.Column
    Debug.Print MaxCell.row

End Sub

There are other ways, probably better to do it - e.g. make a separate function, giving you the column with a given value in a row. This one you would be able to use a lot.

Upvotes: 1

Related Questions