Solved: VBA to write a formula while referencing a Named Range

[JimS]

I’m using VBA to populate some cells with a Percentile Formula but want to use a Range Named Cell as the formulas percentile value variable.

For example, currently the formula is hard-coded to use 0.97 (see below) but I would like it to reference a Named Range Cell so the user can change what percentile they would like to use in the calculation.

I have created a Named Cell labeled “Percent” and referenced it with the following line in my code:

pTile = Range("Percent").Value


How can I use the pTile in the following line of VBA instead of using the 0.97 (currently the hardcoded value)?

.Cells(3, 2) = "=Percentile(B6:B" & LastRow & ", 0.97)"

Also, the user simply enters a whole number in the 'Percent' Named Range Cell (ie: 91, 92, 93, 94, 95, etc) so vba would need to convert the users entry to be in the 0.00 format used in the vba formula.

Can this be done easily?

Thanks for any help…

JimS

[JimS]

I figured it out.

I'm sure there's probably a better way but here's what I did.


pTile1 = Range("Percent").Value
pTile = Left(pTile1, 2)

.Cells(3, 2) = "=Percentile(B6:B" & LastRow & "," & "." & pTile & ")"


JimS

[mdmackillop]

If "PerCent" is format as % then ie shows as 97%
[VBA]
pTile = Range("Percent").Value
Cells(3, 2).Formula = "=Percentile(B6:B" & Lastrow & "," & pTile & ")"
[/VBA]

If it is formated as a general number ie shows as 97
[VBA]
pTile = Range("Percent").Value
Cells(3, 2).Formula = "=Percentile(B6:B" & Lastrow & "," & pTile / 100 & ")"

[/VBA]

[JimS]

Thanks - much cleaner...