PDA

View Full Version : Solved: Problem with formats



davexpc
04-06-2009, 03:31 AM
Hi there!

I was developing my macro in excel 2007 and it was working perfectly but when I tried it in the 2003 version it didn't work because of a format conversion!

The problem is that I write a number in a cell using the percentage format (xlpercentage) so instead of writing 0.05, I write 5.00%

Later in my program, I need to work with that number but when I do

MyRate = ActiveCell.Value (MyRate is defined as double and active cell has the percentage number)

It doesn't work. If I define MyRate as string, the line works but obviously I can't make any calculation with it. Like I said, in the 2007 version there was not any problem.

Any advice?

Thanks in advance!

David

Bob Phillips
04-06-2009, 04:26 AM
Post a workbbok that shows the problem.

davexpc
04-06-2009, 06:38 AM
Sorry I made a mistake. I used FormatPercentage, not xlPercentage. Here you have an example of what I mean:

Dim MyRate as Double

Range("E6").Select
ActiveCell.Value = FormatPercent(0.05)
.....
.....
.....
Range("E6").Select
MyRate = ActiveCell.Value (Now ActiveCell.Value = "5.00%")

This last line won't work because MyRate is double. If I change it to string, the line works, but afterwards I cannot use the value.

I was looking for some sort of instruction that will reverse the action FormatPercentage. Some sort of:

MyRate=UndoFormatPercent(ActiveCell.Value) so that MyRate=0.05

Thanks!!

Kenneth Hobs
04-06-2009, 06:45 AM
Why not use NumberFormat?
e.g.
Range("A1").NumberFormat="0.00%"
Range("A1").Value=0.05

davexpc
04-06-2009, 07:28 AM
Thanks Kenneth!!

You solved my problem! Everything works now.