CODEWITHSUNDEEP
excelvba
altbeb
altbeb

Reputation: 11

How to find the cell address of a range variable in a named range

I am able to do this in Fortran, but I have since discovered that it would be very helpful to have in Excel as well.

I compare three consecutive cells in a named range. When the values in the cells correspond to set criteria, I average the three cells. Then I need to determine the cell address of the central value (cell), thereby allowing me to reference a cell whose offset is dictated by the cell address of this central value cell.

I think it is related to the variable "type" that I declare in the module. 

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

Upvotes: 1

Views: 920

Answers (1)

brettdj
brettdj

Reputation: 55692

For a named range of test you could use this code to find the "central" cell - if by "centra" you mean middle value

The code

  • finds the median of the named range

  • returns the address of this cell from the named range using MATCH and 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

Upvotes: 2

Related Questions