PDA

View Full Version : Solved: VBA to write a formula while referencing a Named Range



JimS
03-24-2010, 07:43 AM
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
03-24-2010, 09:09 AM
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
03-24-2010, 10:49 AM
If "PerCent" is format as % then ie shows as 97%

pTile = Range("Percent").Value
Cells(3, 2).Formula = "=Percentile(B6:B" & Lastrow & "," & pTile & ")"


If it is formated as a general number ie shows as 97

pTile = Range("Percent").Value
Cells(3, 2).Formula = "=Percentile(B6:B" & Lastrow & "," & pTile / 100 & ")"

JimS
03-24-2010, 12:38 PM
Thanks - much cleaner...