Problem using &

D

Daniel Uribe

I made a simple macro that would simply concatenate (using &) two values and directly copy them into another cell.

I did not declared a variable for it, I just wrote this

wsO.Cells(b, 2) = wsI.Cells(a + 1, 1) & wsI.Cells(a + 1, 2)

(I later declared a variable as String equal to wsI.Cells(a + 1, 1) & wsI.Cells(a + 1, 2) and the result is the same)

Being wsO and wsI different workbooks.

Everything seems to work, until I check some values and the following happens:

In some of the resulting values (some are fully numeric and some alphanumeric) the last digits are rounded to 0, for example:
wsI.Cells(a + 1, 1) wsI.Cells(a + 1, 2) wsO.Cells(b, 2)
6705935 000011288 6705935000011280
7220390 000RSM069 7220390000RSM069
7220390 000RSM070 7220390000RSM070
34547272 000000777 34547272000000700
43986229 000000776 43986229000000700
71699014 000RSM072 71699014000RSM072
71699014 000RSM074 71699014000RSM074
71750058 000000781 71750058000000700
800024702 000000462 800024702000000000

Is this an Excel VBA bug? Or is there a way to correct it?

Thanks,

Daniel
 
I

isabelle

hi Daniel,

excel is limited to 15 digits
the way around this is to put "'" & ( text cote) before concatenate

wsO.Cells(b, 2) = "'" & wsI.Cells(a + 1, 1) & wsI.Cells(a + 1, 2)

--
isabelle



Le 2012-11-28 17:45, Daniel Uribe a écrit :
 
D

Daniel Uribe

hi Daniel,



excel is limited to 15 digits

the way around this is to put "'" & ( text cote) before concatenate



wsO.Cells(b, 2) = "'" & wsI.Cells(a + 1, 1) & wsI.Cells(a + 1, 2)



--

isabelle







Le 2012-11-28 17:45, Daniel Uribe a écrit :

Thanks for the help, I tried it and did not work however.
 
C

Claus Busch

Hi Daniel,

Am Wed, 28 Nov 2012 14:45:31 -0800 (PST) schrieb Daniel Uribe:
wsO.Cells(b, 2) = wsI.Cells(a + 1, 1) & wsI.Cells(a + 1, 2)

first format wsO.Range("B1:Bn") as text:

wshO.Range("B1:B" & LRow).NumberFormat = "@"


Regards
Claus Busch
 
J

joeu2004

excel is limited to 15 digits
the way around this is to put "'" & ( text cote) before concatenate
wsO.Cells(b, 2) = "'" & wsI.Cells(a + 1, 1) & wsI.Cells(a + 1, 2)
[....]
I tried it and did not work however.

What do you mean by "did not work"?

Give concrete examples of what does work (if any) and what does not work.



I wonder if the problem is: those cells with numeric values with leading
zeros (e.g. 000011288) have the Custom format 000000000. In that case, the
following might be necessary:

wsO.Cells(b, 2) = "'" & wsI.Cells(a + 1, 1).Text & wsI.Cells(a + 1, 2).Text

There is no need to format the wsO.Cells(b, 2) as Text ("@"). But it would
not hurt to do so. In that case, you would not need ``"'" &``. But you
still might need wsI.Cells(a + 1, 1).Text, for example.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top