Using a variable in a formula

I have defined this range:

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Dim Population As Range
Set Population = Selection

And then I want to use this range in the percentile function, but I haven't
been able to figure it out:

Range("B1").Select
ActiveCell.Formula = "=PERCENTILE(MarketValues,0.2)"

Any help would be greatly appreciated. Thanks.
Noah

Hello Noah,

This should work for you...


Dim marketvalues As Range

marketvalues = Range("A1:A" & Rows.Count).End(xlUp)

Range("B2").Formula = "=PERCENTILE(MarketValues,0.2)"


Sincerely,
Leith Ross

Where are you defining "MarketValues"?

Is MarketValues a named range that is the same as population? If so, this
seemed to work for me:

Sub test()
Dim Population As Range
Set Population = Range("A1", Range("A1").End(xlDown))

Application.Names.Add Name:="Marketvalues", _
RefersTo:=Population

Range("B1").Formula = "=PERCENTILE(MarketValues,0.2)"

End Sub


"Noah" wrote:

> I have defined this range:
>
> Range("A1").Select
> Range(Selection, Selection.End(xlDown)).Select
> Dim Population As Range
> Set Population = Selection
>
> And then I want to use this range in the percentile function, but I haven't
> been able to figure it out:
>
> Range("B1").Select
> ActiveCell.Formula = "=PERCENTILE(MarketValues,0.2)"
>
> Any help would be greatly appreciated. Thanks.
> Noah

I couldn't get it to work w/o some changes. You need a set statement for
marketvalues. For me, the Range statement only returned cell A1. In the
Percentile function, you have to refer to the marketvalues.address as Excel
won't recognize marketvalues (you'll get a NAME error).

Dim marketvalues As Range
Set marketvalues = Range("A1", Range("A" & Rows.Count).End(xlUp))
Range("B2").Formula = "=PERCENTILE(" & marketvalues.Address & ",0.2)"




"Leith Ross" wrote:

>
> Hello Noah,
>
> This should work for you...
>
>
> Dim marketvalues As Range
>
> marketvalues = Range("A1:A" & Rows.Count).End(xlUp)
>
> Range("B2").Formula = "=PERCENTILE(MarketValues,0.2)"
>
>
> Sincerely,
> Leith Ross
>
>
> --
> Leith Ross
> ------------------------------------------------------------------------
> Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
> View this thread: http://www.excelforum.com/showthread...hreadid=480006
>
>