0 is ignored when assigning value to cell in VBA-CodePudding

Why when I do, ActiveCell.Value = "1.10", I end up getting ActiveCell.Value = 1.1?

I would want that whatever is on the right side, which is a string type, would match exactly the content of the cell which I am assigning the value to.

CodePudding user response：

Because they are handled as numbers and mathematically 1.10 and 1.1 is equal!

Handle 1.10 as text

If you use ' before the number

ActiveCell.Value = "'1.10"

it forces Excel to handle it as text/string. Note that then you are not able to calculate with that anymore.

Alternative is setting the number format to taxt and then add the value

ActiveCell.NumberFormat = "@"
ActiveCell.Value = "1.10"

Or something like

Dim foo As Variant
foo = "1.10"

ActiveCell.Value = "'" & foo

Handle 1.1 as number but format it so it looks like 1.10

If you want it to be considered as a number (so you can calculate with it) and still make it look like a 2 digit decimal then use

ActiveCell.Value = "1.1"

and format it correctly

ActiveCell.NumberFormat = "#.00"