I was looking at a spreadhseet populated with a data query. It has a series
of cells that are a code that, in present case, happens to be 5 numeric
digits. As returned by the query, ISTEXT() is TRUE. It is not preceeded by a
'. It is cell formatted General (not Text). But the value in the cell is text
not numeric. Text is OK.

So I'm trying to programatically put this value in a cell in another sheet.
I want it to be ISTEXT(), not proceeded by a ' and cell format General. Just
like the query creates.

I've compared properties of the two cells and nothing is different besides
the type of Value2 being Variant/String in one case and Variant/Double in the
other. Yet just assigning .Value2 = CStr(myStringOfDigits) still results in a
Double in the cell.

Surely if the data query can put a string in the cell as a string, VBA can
do the same??!?

Thanks in advance for any thoughts!

Maybe

dim mystr as string
mystr = cstr(myStringOfDigits)
..value2 = mystr

"Dick Watson" wrote:

I was looking at a spreadhseet populated with a data query. It has a series
of cells that are a code that, in present case, happens to be 5 numeric
digits. As returned by the query, ISTEXT() is TRUE. It is not preceeded by a
'. It is cell formatted General (not Text). But the value in the cell is text
not numeric. Text is OK.

So I'm trying to programatically put this value in a cell in another sheet.
I want it to be ISTEXT(), not proceeded by a ' and cell format General. Just
like the query creates.

I've compared properties of the two cells and nothing is different besides
the type of Value2 being Variant/String in one case and Variant/Double in the
other. Yet just assigning .Value2 = CStr(myStringOfDigits) still results in a
Double in the cell.

Surely if the data query can put a string in the cell as a string, VBA can
do the same??!?

Thanks in advance for any thoughts!

Have you tried formatting the destination cell(s) as Text before you
put the data in them?

I think you will have to format the cell as Text. Using A1 and 123 as an
example...

Range("A1").NumberFormat = "@"
Range("A1").Value = 123

--
Rick (MVP - Excel)


"Dick Watson" wrote in
message ...
I was looking at a spreadhseet populated with a data query. It has a series
of cells that are a code that, in present case, happens to be 5 numeric
digits. As returned by the query, ISTEXT() is TRUE. It is not preceeded by
a
'. It is cell formatted General (not Text). But the value in the cell is
text
not numeric. Text is OK.

So I'm trying to programatically put this value in a cell in another
sheet.
I want it to be ISTEXT(), not proceeded by a ' and cell format General.
Just
like the query creates.

I've compared properties of the two cells and nothing is different besides
the type of Value2 being Variant/String in one case and Variant/Double in
the
other. Yet just assigning .Value2 = CStr(myStringOfDigits) still results
in a
Double in the cell.

Surely if the data query can put a string in the cell as a string, VBA can
do the same??!?

Thanks in advance for any thoughts!

Nope. Excel is "smarter" than that...

Thanks for posting though.

"Sam Wilson" wrote:

Maybe

dim mystr as string
mystr = cstr(myStringOfDigits)
.value2 = mystr

That's what I was hoping to avoid--not for any particular reason besides the
intellectual curiousity that the retrieved query data didn't need Text but
could store the number as text, so why couldn't I?

What I ended up doing was setting the NumberFormat = "@", assigning the
value, then setting back the NumberFormat = "General". Of course, then the
cell error XlNumberAsText played. So then I ended up setting this error to
Ignore = True. Of course, the data saved by the query didn't have that error
supressed, so I was back full circle. I still don't know how they do it and
why the same cannot be done from VBA.

"norie" wrote:

Have you tried formatting the destination cell(s) as Text before you
put the data in them?

That's exactly what I ended up doing (more or less, see another posting for
more specifics) but I still wonder how the query gets the number stored as
text, General format, no XlNumberAsText error, no ', and yet it seems
impossible to do the same from VBA.

Thanks for replying.

"Rick Rothstein" wrote:

I think you will have to format the cell as Text. Using A1 and 123 as an
example...

Range("A1").NumberFormat = "@"
Range("A1").Value = 123

Does the entry returned by the query perhaps have a space (or some other
non-visible character) in front or after the number? You should be able to
check with the LEN function. If it is an "invisible" character of some sort,
then this VB code should uncover it...

Sub ShowASCIIvalues()
Dim X As Long
For X = 1 To Len(Selection.Value)
Debug.Print "Position: " & X & " - ASCII value: " & _
Asc(Mid(Selection.Value, X, 1))
Next
End Sub

--
Rick (MVP - Excel)


"Dick Watson" wrote in
message ...
That's exactly what I ended up doing (more or less, see another posting
for
more specifics) but I still wonder how the query gets the number stored as
text, General format, no XlNumberAsText error, no ', and yet it seems
impossible to do the same from VBA.

Thanks for replying.

"Rick Rothstein" wrote:

I think you will have to format the cell as Text. Using A1 and 123 as an
example...

Range("A1").NumberFormat = "@"
Range("A1").Value = 123

Checked that very early on. It is just 5 ASCII characters within 0 .. 9.
Exactly what's in the database.

"Rick Rothstein" wrote:

Does the entry returned by the query perhaps have a space (or some other
non-visible character) in front or after the number? You should be able to
check with the LEN function. If it is an "invisible" character of some sort,
then this VB code should uncover it...