CODEWITHSUNDEEP
vbaexcel
Bob
Bob

Reputation: 101

VBA Currency to Text Conversion

I have a list of currency values in one WorkSheet such as $4,250.00 that I want to compare to the equivalent text/string; e.g. "$4,250" in another Worksheet. When I convert the currency value to text prior to the InStr comparison, I lose the "$" and "," resulting in "4250" which makes the comparison false. Is there a way to convert the currency value to text and not lose the format?

Thanks

Upvotes: 0

Views: 4801

Answers (1)

YowE3K
YowE3K

Reputation: 23974

A numeric value can be formatted to currency in VBA using Format(value, "Currency"), e.g. Format(Range("A1").Value, "Currency").

Also, the .Text property will return the formatted string, so Range("A1").Text would give you a string of $4,250.00 if cell A1 contained 4250 and was formatted in a standard currency format.

In Excel itself, I would suggest using a formula such as =TEXT(A1,"$#,##0.00").

Upvotes: 2

Related Questions