CODEWITHSUNDEEP
vbaexcel
RGriffiths
RGriffiths

Reputation: 5970

Decimal place issue in Excel

I am trying to use VBA to place into a cell a number to two decimal places followed by text in brackets in a cell such as:

1.23 (text)

An example of how I am acheiving this is as follows:

Cells(1, 1) = 1.23
Cells(1, 2) = Cells(1, 1) & " (text)"

The format of Cells(1, 1) is to two decimal places on the worksheet but if this number is 1.20 or 1.00 for example the result is:

1.2 (text) or 1 (text)

How can I force the two decimal places to be maintained in Cells(1, 2) to be to two decimal places? I have tried:

Cells(1, 2) = Round(Cells(1, 1), 2) & " (text)"

Cells(1, 1).NumberFormat = "0.00"
Cells(1, 2) = Cells(1, 1) & " (text)"

but neither of these work. Any suggestions?

Upvotes: 1

Views: 142

Answers (1)

Barranka
Barranka

Reputation: 21047

You can use the Format() function:

Cells(1,2) = Format(Cells(1,1), "0.00") & "(text)"

Or maybe you can apply a number format to the cell directly, instead of converting it into a text value. In the cell number format properties, use this format:

0.00" (text)";-0.00" (other text);"0.00"

Upvotes: 3

Related Questions