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
88 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA: Select SQL query based on a config Sheet v2 11 49
Excel Spacing Anomaly 4 36
what is the best open source database solution? 3 43
Set WorkSheet  not Working 9 42
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Viewers will learn how to find and create templates in Excel 2013.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

828 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