CODEWITHSUNDEEP
stringexcelvbatype-conversion
Derényi István
Derényi István

Reputation: 111

how to write value into cell with vba code without auto type conversion?

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,

Upvotes: 6

Views: 109629

Answers (3)

im_Spartacus
im_Spartacus

Reputation: 21

This is probably too late, but I had a similar problem with dates that I wanted entered into cells from a text variable. Inevitably, it converted my variable text value to a date. What I finally had to do was concatentate a ' to the string variable and then put it in the cell like this:

prvt_rng_WrkSht.Cells(prvt_rng_WrkSht.Rows.Count, cnst_int_Col_Start_Date).Formula = "'" & _ 
    param_cls_shift.Start_Date (string property of my class)

Upvotes: 0

Alexandre
Alexandre

Reputation: 120

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"

Upvotes: 9

SeanC
SeanC

Reputation: 15923

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.

Upvotes: 11

Related Questions