Ok, yesterday I asked how to append some information on to the end of
a file. Today I have a question with some twists to it.

1)
In Column 'C' I have a product number field. It consists of 5
digits. An example of a product number would be. 00010 The zero's in
front of the 10 are important.
I'll give you other examples so you can fully understand how many
numbers I might have.
00010
00100
01000
10000
Those are basic examples of what they might look like..could be any
counting numbers, but will always have the 0 place holder to bring the
digits to a total of 5.

2)
In Column 'B' I have free form product name. I.e. Widget or Very Very
small widget
I need to append the values in Column 'C' to the end of Text in Column
'B'

My data should look something like this..

Field B2 reads Widget
Field B3 reads Very Very small widget
Field C2 reads 00100
Field C3 reads 00010

I want my final data output in Field B2 and B3 to read
B2 Widget 00100
B3 Very Very small widget 00010

If you have any questions..please feel free to ask.

Thanks

hi,

This is posted in the Programming forum but here are a couple of
non-vba solutions...

1)
- select the cells, press [ctrl + 1], Number - Custom, & type 00000
into the Type field.
- Or, using a helper column eg column D, type
Code:
--------------------
=TEXT(C1,"00000")
--------------------
& copy down as needed.

2)
- In a helper column, eg column D, type
Code:
--------------------
=B2 & " " & C2
--------------------
, copy down as needed, select all the cells, press [ctrl + c], select
cell B2 & press [alt + E + S + V] to paste special as values over the
top of the original data.

If you do still want a macro solution, record a macro as you complete
the actions manually & then post the recorded code if you need help
making it flexible.

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144631

Give this code a try..

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

--
Rick (MVP - Excel)


"broro183" wrote in message
...

hi,

This is posted in the Programming forum but here are a couple of
non-vba solutions...

1)
- select the cells, press [ctrl + 1], Number - Custom, & type 00000
into the Type field.
- Or, using a helper column eg column D, type
Code:
--------------------
=TEXT(C1,"00000")
--------------------
& copy down as needed.

2)
- In a helper column, eg column D, type
Code:
--------------------
=B2 & " " & C2
--------------------
, copy down as needed, select all the cells, press [ctrl + c], select
cell B2 & press [alt + E + S + V] to paste special as values over the
top of the original data.

If you do still want a macro solution, record a macro as you complete
the actions manually & then post the recorded code if you need help
making it flexible.

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile:
http://www.thecodecage.com/forumz/member.php?userid=333
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=144631

On Oct 15, 3:11*pm, "Rick Rothstein"
wrote:
Give this code a try..

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

--
Rick (MVP - Excel)

"broro183" wrote in message

...



hi,

This is posted in the Programming forum but here are a couple of
non-vba solutions...

1)
- select the cells, press [ctrl + 1], Number - Custom, & type 00000
into the Type field.
- Or, using a helper column eg column D, type
Code:
--------------------
* *=TEXT(C1,"00000")
--------------------
& copy down as needed.

2)
- In a helper column, eg column D, type
Code:
--------------------
* *=B2 & " " & C2
--------------------
, copy down as needed, select all the cells, press [ctrl + c], select
cell B2 & press [alt + E + S + V] to paste special as values over the
top of the original data.

If you do still want a macro solution, record a macro as you complete
the actions manually & then post the recorded code if you need help
making it flexible.

hth
Rob

--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile:
http://www.thecodecage.com/forumz/member.php?userid=333
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=144631



The code populates my description field perfectly. I'll be sure and
use it.
Thank You so much.

On Oct 15, 2:03*pm, broro183 wrote:
hi,

This is posted in the Programming forum but here are a couple of
non-vba solutions...

1)
- select the cells, press [ctrl + 1], Number - Custom, & type 00000
into the Type field.
- Or, using a helper column eg column D, type
Code:
--------------------
* * =TEXT(C1,"00000")
--------------------
*& copy down as needed.

2)
- In a helper column, eg column D, type
Code:
--------------------
* * =B2 & " " & C2
--------------------
, copy down as needed, select all the cells, press [ctrl + c], select
cell B2 & press [alt + E + S + V] to paste special as values over the
top of the original data.

If you do still want a macro solution, record a macro as you complete
the actions manually & then post the recorded code if you need help
making it flexible.

hth
Rob

--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile:http://www.thecodecage.com/forumz/member.php?userid=333
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=144631

The Text formatting works great. I think the VBA code below works
easiest for migrating the B and C column. Even though the VBA code
works..I really like the flexibilty of using the helper column also to
join the columns. Each have their application. Thank you so much.

hi,

Thanks for the feedback - I'm pleased we could help :)

Here's a slight twist on Rick's vba approach using arrays which -may be
slightly- quicker if you have a lot of rows of data to modify.


Code:
--------------------
Option Explicit

Sub ConcatBandC_UsingArrays()
Dim x As Long, LastRow As Long
Dim TempArrB As Variant
Dim TempArrC As Variant
With ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range(Cells(2, 2), Cells(LastRow, 2))
'populate temporary arrays with data from the sheet
TempArrB = .Value2
TempArrC = .Offset(0, 1).Value2
'loop through the array & modify it (in memory)
For x = LBound(TempArrB) To UBound(TempArrB)
TempArrB(x, 1) = TempArrB(x, 1) & Format(TempArrC(x, 1), " 00000")
Next x
'write array back to the spreadsheet
.Value2 = TempArrB
End With
End With
End Sub

--------------------


Note that the last action of writing the array back to the range may
(?) be subject to the limitations of copying vba arrays to worksheet
ranges which are discussed in the below links:
'Daily Dose of Excel » Blog Archive » Writing To A Range Using VBA'
(http://tinyurl.com/yhfzqj8)
'XL: Limitations of Passing Arrays to Excel Using Automation'
(http://support.microsoft.com/kb/177991)

Rick, I know you've given a quick solution (like I did in my initial
post) but I think it's important to explicitly define range objects
(even if it is just to the active sheet) because this makes OP's aware
of where the changes will occur/what data will be used - whereas not all
OP's know that the default of "range(..." is to the active sheet esp if
they are also impacting other sheets within the code.

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=144631