Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
95 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 800 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 31

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 31

Accepted Solution

by:
gowflow earned 1200 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

Technology Partners: 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

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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.

604 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