Find last cell with function output data and output new result

Question

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

The code defining and populating FunctionRange seems pretty fine to me. What about the rest (= everything)? I guess (hope) that this is not your best attempt. — varocarbas
Your last comment has been frankly brilliant: what is the exact point of such a request? Also what is the exact point of moving to VBA anyway? Cannot you do that just via formulae? — varocarbas
@Varocarbas Working on alternatives, and def attainable...looking to see how others might solve it. — Bob Hopez
You should know that this is not how things work here: you show us your code, your efforts, why you tried everything and why it didn't work, etc. and we propose solutions. But we are not supposed to write the whole code for you. — varocarbas

Bob Hopez Bob Hopez · Accepted Answer · 2013-11-04T20:13:29

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?

Find last cell with function output data and output new result

1 Answers