如何在命名区域中查找区域变量的单元格地址

Question

我可以在Fortran中做到这一点，但后来我发现在Excel中也有很大帮助。

我比较命名范围中的三个连续单元格。当单元格中的值与设置的标准相对应时，我对三个单元格取平均值。然后，我需要确定中心值（单元格）的单元格地址，从而允许我引用一个单元格，该单元格的偏移量由该中心值单元格的单元格地址决定。

我认为这与我在模块中声明的变量"类型"有关。

Dim CellAddressMin As Range
Dim CellAddressMax As Range
Dim i As Integer
Dim MaxVal As Double
Dim MinVal As Double
Dim Y1Value As Variant, YValue As Variant, Y2Value As Variant
 SOME OTHER CODE
ElseIf (Y1Value >= YValue And Y2Value >= YValue) Then
    ' Y Value is a minimum and need to determine the corresponding time interval
    'The minimum value will be taken as the average of the prior, the minimum and the proceeding values
    MinVal = WorksheetFunction.Average(Y1Value, YValue, Y2Value)
    CellAddressMin = YValue.address

Answer 1

对于test的命名范围，您可以使用此代码来查找"中心"单元格-如果"centra"是指中间值

代码

• 查找命名范围的中值

• 使用MATCH和INDEX 从命名范围返回此单元格的地址

  Sub GetMedian()
  Dim rng1 As Range
  Dim rng2 As Range
  Set rng1 = Range("test")
  Set rng2 = Application.Index(rng1, Application.Match(Application.Median(rng1), rng1, 0))
  MsgBox rng2.Address(0, 0)
  End Sub