CODEWITHSUNDEEP
vbaexcelfunctional-programmingdynamic-programming
Bob Hopez
Bob Hopez

Reputation: 783

Find last cell with function output data and output new result

This problem uses the following data, which would be manually adaptive over a fixed cell range--with each cell in the B column range containing a formula. It aims to find the last data cell from the underlying formula cells.

Given data

I would like to find the last formula cell with data within the formula range B2:B11, and create a dynamic median from this last cell with the four cells above it. The median should be output to cell F6--result of 9. This is a dynamic exercise. Any thoughts on how to do this most efficiently, given the code below?

Sub OutputMedian()

 Dim FunctionRange As Range

    'Represents a fixed range with function in B2:B11
    Set FunctionRange = Worksheets("Sheet1").Range("B2:B11")


   'Must start median calc from B9, as it's the last cell with function output data

   'Must store Median from last data cell, using 5 cell offset (see output from cell F2)

   'Must output the Final (e.g., median output of 9 here) to cell F6


End Sub

Upvotes: 0

Views: 333

Answers (1)

Bob Hopez
Bob Hopez

Reputation: 783

See: Excel VBA: Get Last Cell Containing Data within Selected Range

My modified answer by @brettdj from above question (referred by @varocarbas). Thanks!

Got it to work!! Outputs the correct dynamic median, with five periods set from -4 Offset below.

Sub OutputMedian()

    Dim WS As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range

    Set WS = Sheets("Sheet1")
    Set rng1 = WS.Columns("B:B").Find("*", Range("B1"), xlValues, , xlByRows, xlPrevious)
    Set rng2 = rng1.Offset(-4, 0)

    Dim FirstCell As String
    Dim LastCell As String

    FirstCell = rng2.Address(0, 0)
    LastCell = rng1.Address(0, 0)

    Dim CellResponse As String
    CellResponse = Evaluate("=median(" & FirstCell & ":" & LastCell & ")")
    Range("F6").Value = CellResponse


End Sub

Better way to use objects (e.g., R1C1, Cells) in creating dynamic functions--i.e, without passing function into Evaluate as concatenated strings?

Upvotes: 0

Related Questions