jnash67
asked on
Excel VBA - Format a string with the NumberFormat of a cell
I have some calculated values that depend on a selected range. I want to format these calculated values with the NumberFormat of the selected range. I'm assuming they all have the same NumberFormat so I just take the numberformat of the first cell.
I am currently using the NumberFormat as follows:
columnFormat = cell.NumberFormat
stringNum = cstr(format(doubleNum, columnFormat)
Unfortunately, it doesn't work in a lot of cases, like when the format is "General" or a rather complex one we use that works fine in the cell but shows positive numbers ending with a "_)".
Is there a more robust way to do this? Do I have to have a big list of exceptions to check for?
I am currently using the NumberFormat as follows:
columnFormat = cell.NumberFormat
stringNum = cstr(format(doubleNum, columnFormat)
Unfortunately, it doesn't work in a lot of cases, like when the format is "General" or a rather complex one we use that works fine in the cell but shows positive numbers ending with a "_)".
Is there a more robust way to do this? Do I have to have a big list of exceptions to check for?
ASKER
Do you still need the cstr() around it since WorksheetFunction.Text seems to convert the number to text already?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for speedy response
You could use the WorksheetFunction.Text to do this (note this will be a little slower than using Format):
Richard
Open in new window