• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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.

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
EdLB
Asked:
EdLB
  • 2
  • 2
2 Solutions
 
Michael FowlerSolutions ConsultantCommented:
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
 
gowflowCommented:
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
 
EdLBAuthor Commented:
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
 
gowflowCommented:
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
 
EdLBAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now