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
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 23

Assisted Solution

by:Michael Fowler
Michael Fowler 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 30

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 30

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn the different options available in the Backstage view in Excel 2013.
Viewers will learn the basics about Excel 2013’s new Flash Fill feature.

696 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