VBA (Macro) - Function with Variable Array

[katie in cambridge]

I apologize if this is obvious or has been answered elsewhere (I've searched high and low and haven't come across it, even my manuals/books I am using to learn this stuff).

I'm new to VBAs and macros.

I'm trying to create a macro that will return the 50th percentile (median) of a column. I can use the record function to create such a macro:

Range("C18").Select
    ActiveCell.FormulaR1C1 = "=PERCENTILE.INC(R[-16]C:R[-3]C,0.5)"
    Range("C19").Select

This example is from dummy data I was playing with and works great...for the 15 lines of data in that column.

The problem is that my macro needs to account for the fact that there will be different numbers of cells in the column on occasion. Sometimes I will have 1000 cells in the column with data, other times 2000, and so on.

On top of this, I would like the median to be returned three lines underneath the last active cell (and oh, yeah, the data is noncontiguous).

I tried using

Range(ActiveCell, ActiveCell.End(xlDown)).Select</pre>

but it didn't work or at least I didn't do it correctly.

I have also tried to use

Sub LastCellInColumn()  Range("C65536").End(xlup).Select  End Sub

.

I have absolutely no idea how to move the returned value relative to the variable array.

Is this hopeless? Thanks, Katie

 

[exceldevs]

Hi Katie,

Are you looking for this?

Sub Gen_Formula()
    Range("C" & Rows.Count).End(xlUp).Offset(3).FormulaR1C1 = "=PERCENTILE(R2C:R[-3]C,0.5)"
End Sub

 

[katie in cambridge]

Thank you SO MUCH exceldevs! You are a miracle worker. This is perfect. I feel kind of dumb now - spent hours trying to figure this out!

If I wanted this to run in the active cell column (without regard to "C" or what have you - whatever column my cursor was in, for example), would I modify the range to include a reference to activecell?

 

[exceldevs]

You're welcome.

Try:

Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(3).FormulaR1C1 = "=PERCENTILE(R2C:R[-3]C,0.5)"