确定最小值在多个单元格中的位置?

Question

要找到范围内最小值的行位置，我只需执行以下操作：

Dim rng As Range
Dim dblMin As Double
Set rng = Sheet1.Range("A1:A10")
dblMin = Application.Match(Application.Min(rng), rng, 0)
End Sub

如何检查特定的单元格A1，A3和A6并调整我的范围？

我尝试了类似Min(A1,A3,A6)的东西，如何更改查找范围？A1&A3&A6 or A1,A3,A6不起作用...

理想情况下，我想返回行位置 1 或 2 或 3，因为我们只比较 3 个值。

Answer 1

尝试以下代码：

Dim Rng As Range, FindRng As Range
Dim dblMin As Double
' set the Range
Set Rng = Application.Union(Sheet1.Range("A1"), Sheet1.Range("A3"), Sheet1.Range("A6"))
' find the minimum value
dblMin = WorksheetFunction.Min(Rng)
' use Find to find the Minimum value in your range
Set FindRng = Rng.Find(what:=dblMin, LookIn:=xlValues, LookAt:=xlWhole)
MsgBox "Minimum found at " & FindRng.Address(False, False, xlA1)

---

编辑1- 获取区域内单元格的顺序

修改后的代码

Dim Rng As Range, C As Range
Dim dblMin As Double, i As Long, MatchRow As Variant
Dim Arr() As Double
' set the Range
Set Rng = Application.Union(Sheet1.Range("A1"), Sheet1.Range("A3"), Sheet1.Range("A6"))
' populate Array from values in Range
ReDim Arr(1 To Rng.Cells.Count)
i = 1
For Each C In Rng
Arr(i) = C.Value
i = i + 1
Next C
' find the minimum value
dblMin = WorksheetFunction.Min(Rng)
' use Match to find the element order inside the array
MatchRow = Application.Match(dblMin, Arr, 0)
MsgBox MatchRow

Answer 2

伪代码：

Dim rng1 as Range
Dim rng2 as Range
Dim rng3 as Range
Dim lMin as Double
Set rng1 = Sheet1.Range("A1")
Set rng2 = Sheet1.Range("A2")
Set rng3 = Sheet1.Range("A6")
lMin = Application.WorksheetFunction.Min(rng1.Value, rng2.Value, rng3.Value)
Select Case lMin
Case is = rng1
Debug.Print "The range rng1 contains the smallest value."
' You can insert code here to do something when rng1 is minimum...
Case is = rng2
Debug.Print "The range rng2 contains the smallest value."
' You can insert code here to do something when rng2 is minimum...
Case is = rng3
Debug.Print "The range rng3 contains the smallest value."
' You can insert code here to do something when rng3 is minimum...
End Select

Answer 3

IF Application.min(rng) = Sheet1.range("A1").value then
dblMin = 1
Else If Application.min(rng) = sheet1.range("A3").value then
dblMin = 2
Else
dblMin = 3
End If

尚未测试语法。 如果单元格等于最小值，则将是嵌套 IF 到文本的一般概念。