Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected issue with VBA code received
What the code does is moves the number data in excel from column C and
appends it to B. It also formats that data to be in a "00000" data format because the source data on my example from column C row 1 would look like 11. The end result is it might look like this. Widget 00011 My unexpected issue is that now I have some data that I believe excel doesn't treat as a number. Some of the data Example source Column C row 2 = 11602-72401 Column B row 2 =Small Widget end result = Small Widget11602-72401 as you can see the space between the txt and number don't exist. I think because Excel treats the 11602-72401 number as text. Here is the sample code: Sub ConcatBandC() Dim X As Long, LastRow As Long LastRow = Cells(Rows.Count, "B").End(xlUp).Row For X = 2 To LastRow Cells(X, "B").Value = Cells(X, "B").Value & Format( _ Cells(X, "C").Value, " 00000") Next End Sub Any ideas on fixing the code so it will handle both examples? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected issue with VBA code received
Well, 11602-72401 might be a number to you; but, since pure numbers do not
have symbols in the middle of them, then both excel and VB will see it as text. However, if you move the space character outside of the Format function's format pattern, I think your code will do what you want. Try it this way and see if it works... Cells(X, "B").Value = Cells(X, "B").Value & " " & Format( _ Cells(X, "C").Value, "00000") -- Rick (MVP - Excel) "srosetti" wrote in message ... What the code does is moves the number data in excel from column C and appends it to B. It also formats that data to be in a "00000" data format because the source data on my example from column C row 1 would look like 11. The end result is it might look like this. Widget 00011 My unexpected issue is that now I have some data that I believe excel doesn't treat as a number. Some of the data Example source Column C row 2 = 11602-72401 Column B row 2 =Small Widget end result = Small Widget11602-72401 as you can see the space between the txt and number don't exist. I think because Excel treats the 11602-72401 number as text. Here is the sample code: Sub ConcatBandC() Dim X As Long, LastRow As Long LastRow = Cells(Rows.Count, "B").End(xlUp).Row For X = 2 To LastRow Cells(X, "B").Value = Cells(X, "B").Value & Format( _ Cells(X, "C").Value, " 00000") Next End Sub Any ideas on fixing the code so it will handle both examples? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unexpected issue with VBA code received
On Oct 28, 8:46*am, "Rick Rothstein"
wrote: Well, 11602-72401 might be a number to you; but, since pure numbers do not have symbols in the middle of them, then both excel and VB will see it as text. However, if you move the space character outside of the Format function's format pattern, I think your code will do what you want. Try it this way and see if it works... * * Cells(X, "B").Value = Cells(X, "B").Value & " " & Format( _ * * * * * * * * * * * * * Cells(X, "C").Value, "00000") -- Rick (MVP - Excel) "srosetti" wrote in message ... What the code does is moves the number data in excel from column C and appends it to B. It also formats that data to be in a "00000" data format because the source data on my example from column C row 1 would look like 11. The end result is it might look like this. Widget 00011 My unexpected issue is that now I have some data that I believe excel doesn't treat as a number. *Some of the data Example source Column C row 2 = 11602-72401 * * Column B row 2 =Small Widget end result = *Small Widget11602-72401 as you can see the space between the txt and number don't exist. *I think because Excel treats the 11602-72401 number as text. Here is the sample code: Sub ConcatBandC() *Dim X As Long, LastRow As Long *LastRow = Cells(Rows.Count, "B").End(xlUp).Row *For X = 2 To LastRow * *Cells(X, "B").Value = Cells(X, "B").Value & Format( _ * * * * * * * * * * * * *Cells(X, "C").Value, " 00000") *Next End Sub Any ideas on fixing the code so it will handle both examples? Thanks It works great. Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code issue | Excel Programming | |||
Code issue | Excel Programming | |||
Pause code, wait for input, no input received, carry on with the code | Excel Programming | |||
VBA Code issue | Excel Programming | |||
unexpected problem in VBA Code | Excel Programming |