Solved: Problem with formats

[davexpc]

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]

Post a workbbok that shows the problem.

[davexpc]

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]

Why not use NumberFormat?
e.g.
[VBA]Range("A1").NumberFormat="0.00%"
Range("A1").Value=0.05[/VBA]

[davexpc]

Thanks Kenneth!!

You solved my problem! Everything works now.