在一个范围内获取具有最小值的单元格的值

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.

然后,我´试图让马克斯的列值。我的目标是让C或C1能够知道NY (C1)是具有最大值的那个。

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

2 个解决方案

#1

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.

还有其他更好的方法——例如，创建一个单独的函数，给你一行中给定值的列。这个你可以用很多。

#2

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

编辑:哎呀，有人跑得更快了

#1