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?
Last Comment
ASKER
Do you still need the cstr() around it since WorksheetFunction.Text seems to convert the number to text already?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for speedy response
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
You could use the WorksheetFunction.Text to do this (note this will be a little slower than using Format):
Richard
Open in new window