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, Nov 04 '13 at 18:34
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, Nov 04 '13 at 18:36
@Varocarbas Working on alternatives, and def attainable...looking to see how others might solve it. — Bob Hopez, Nov 04 '13 at 18:36
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, Nov 04 '13 at 18:37
Regarding the dynamic thing; VBA can certainly do many more things than Excel formulae. But, in certain contexts, it is better to rely on formulae because it is quicker: if you account for what you want with formulae, the results would be updated immediately; by relying on VBA (either a loop or an unnecessarily big set of repeated lines), it would certainly be slower (although might not be noticiable for a so small sample). — varocarbas, Nov 04 '13 at 18:39
@Varocarbas ...noted...this is my best attempt at this moment. Don't know how to find last cell with output from function range. — Bob Hopez, Nov 04 '13 at 18:40
http://stackoverflow.com/questions/8785063/excel-vba-get-last-cell-containing-data-within-selected-range — varocarbas, Nov 04 '13 at 18:41
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40528/discussion-between-bob-hopez-and-varocarbas) — Bob Hopez, Nov 04 '13 at 19:36

score 0 · Accepted Answer · edited May 23 '17 at 12:06

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?

Might help in getting median from two strings "B9" and "B5", outputting value and not formula Final: http://www.mrexcel.com/forum/excel-questions/237823-convert-address-string-r1c1.html — Bob Hopez, Nov 04 '13 at 20:17

Find last cell with function output data and output new result

1 Answers1