Solved

Using EXCEL VBA, I'd like to concatenate the values in three columns together in a fourth column. Last row of the three columns is the same but it is a variable.

Posted on 2015-01-27
5
91 Views
Last Modified: 2015-01-28
I get a list of parts which changes from time to time and establish three columns of information. I would like to concatenate the three columns together and put the concatentated values in a forth column. I'm wondering is this is possible without a loop and a counter? See example is attached file.
Concatenate-Example.xlsx
0
Comment
Question by:EdLB
  • 2
  • 2
5 Comments
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 200 total points
ID: 40574471
How about just using a formula for this. It neatly avoids using a loop

The following formula dragged down the column gives the result you are after
=A2 & " : " & B2 & " : " & C2 & " : "  & D2

Open in new window

Concatenate-Example.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40575230
Put this formula in E2 and drag as much as you want down. If your last row is variable then drag for way to much like 200+rows and then when its blank in A you will have nothing in E

=IF(A2<>"",A2&" : "&B2&" : "&C2&" : "&D2,"")

gowflow
0
 

Author Comment

by:EdLB
ID: 40575299
I am trying to accomplish this using VB. The code I tried is below but VB does not like it. Suggestions?
    For Cntr2 = 2 To lastRow
 
        tempS = Range("B" & Trim(Str(Cntr2))) & " : " & Range("C" & Trim(Str(Cntr2))) & " : " & Range("D" & Trim(Str(Cntr2)))
       
        Range("E" & Cntr2).Value = tempS
    Next Cntr2
0
 
LVL 29

Accepted Solution

by:
gowflow earned 300 total points
ID: 40575364
Dim Cntr2 as Long
Dim temps as String

For Cntr2 = 2 To lastRow
  
         tempS = Range("B" & Cntr2) & " : " & Range("C" & Cntr2) & " : " & Range("D" & Cntr2)
         Range("E" & Cntr2).Value = tempS
Next Cntr2 

Open in new window


Your missing Col A though !!

gowflow
0
 

Author Closing Comment

by:EdLB
ID: 40575554
Thanks guys, I was aware of the formula. It was the VB that was giving me problems. That's a great help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question