Menu
This problem seems very simple, yet I just can not find the solution (I am already loosing my mind about it :) )
OK, so I just want to put a certain value into an excel cell, using vba code, just as simple as this:
Cells(1,1).Value2 = "123,456"
The problem: this is a string (intentionally), but excel always convert it to number, and put that number into the cell, instead of the string that I wanted.
How can I force excel not to convert it, and just put into the cell exactly what I want (the string)??
Thanks,
734
If you want a user to specify a value to enter in a cell you can use an input box. Let's say you want to enter the value in cell A1, the code would be like this: Range("A1"). Value = _ InputBox(Prompt:="Type the value you want enter in A1.")
For the defined variable, put an equal sign and mention the cell address. Once again, put a dot to see the IntelliSense list. From the VBA IntelliSense list, choose “Value” property to get the value from the mentioned cell. Now the variable “CellValue” holds the value from the cell A1.
When we declare a variable, we need to assign a data type to it, and we can also assign objects as data types. To assign a value to declared object variables, we need to use the word “SET.” The word “Set” used to refer to a new object in VBA, for example, referring to the particular range of the particular worksheet.
Type the formula =SpellNumber(A1) into the cell where you want to display a written number, where A1 is the cell containing the number you want to convert. You can also manually type the value like =SpellNumber(22.50).
Cells(1,1).Value2 = "'123,456"
note the single apostrophe before the number - this will signal to excel that whatever follows has to be interpreted as text.
131
Indeed, just as commented by Tim Williams, the way to make it work is pre-formatting as text. Thus, to do it all via VBA, just do that:
Cells(1, 1).NumberFormat = "@"
Cells(1, 1).Value = "1234,56"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
