modification for the code

Hi
The following code will show result in percentage with left aligned(TEXT) in
excel. My requirement is that the result should be right aligned (in number
format)
with the same percentage symbol along with the result. ( rather than
text with left aligned). Otherwise the code works fine and no change is
required.

Public Function CAGR(FirstValue, LastValue)
Dim fv, lv, v As Double
Set r = Application.Range(FirstValue, LastValue)
Count = (Application.Range(FirstValue, LastValue).Count - 1)
CAGR = FirstValue
v = Application.WorksheetFunction.Rate(Count, 0, -(FirstValue), LastValue)
CAGR = Format(v, "00.00%")
End Function

I am repeatedly asking for help but no avail. Will any one familiar with
this area would kindly help me. As I am not familar with the VBA and got this
thro one of my friend I am helpless.

Although your code works fine it is also a bit ugly

but you should look at:
HorizontalAlignment = xlRight

Tip: if you start the macro editor and record a macro doing what yopu want to achieve and stop it the vba code of this macro is stored in a new module.

Just change this line

CAGR = Format(v, "00.00%")

to

CAGR = v

and format the cell in Excel. A UDF cannot format the cell, just return an
answer.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"srinivasan" <[email protected]> wrote in message
news:[email protected]...
> Hi
> The following code will show result in percentage with left aligned(TEXT)
in
> excel. My requirement is that the result should be right aligned (in
number
> format)
> with the same percentage symbol along with the result. ( rather than
> text with left aligned). Otherwise the code works fine and no change is
> required.
>
> Public Function CAGR(FirstValue, LastValue)
> Dim fv, lv, v As Double
> Set r = Application.Range(FirstValue, LastValue)
> Count = (Application.Range(FirstValue, LastValue).Count - 1)
> CAGR = FirstValue
> v = Application.WorksheetFunction.Rate(Count, 0, -(FirstValue), LastValue)
> CAGR = Format(v, "00.00%")
> End Function
>
> I am repeatedly asking for help but no avail. Will any one familiar with
> this area would kindly help me. As I am not familar with the VBA and got
this
> thro one of my friend I am helpless.
>