Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using &
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using &
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 : 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using &
On Wednesday, November 28, 2012 6:44:55 PM UTC-5, isabelle wrote:
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 : 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 Thanks for the help, I tried it and did not work however. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using &
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem using &
"Daniel Uribe" wrote:
On Wednesday, November 28, 2012 6:44:55 PM UTC-5, isabelle wrote: 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. "Daniel Uribe" wrote previously: wsI.Cells(a + 1, 1) wsI.Cells(a + 1, 2) wsO.Cells(b, 2) 6705935 000011288 6705935000011280 7220390 000RSM069 7220390000RSM069 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel startup problem. Possible problem with stdole32.tlb | Excel Programming | |||
Colon at the end of excel file name(ex: problem.xls:1, problem.xls | New Users to Excel | |||
Started out as an Access problem. Now an Excel problem | Excel Discussion (Misc queries) | |||
Problem when multipple users access shared xl-file at the same time, macrocode for solve this problem? | Excel Programming |