Horizontal merge into new cell

[Westbury]

How do I use vba to merge the contents of 3 columns on one row into a new cell on that row with each former cell separated with a comma and the last entry in quotation marks?

e.g.

AA BB CC would merge to AA,BB,"CC" in a new cell and the process repeats for n rows

I've found lots of ideas to create a single column from data but not to execute a horizontal merge.

Thanks

 

[mumps]

Try this formula: =A1&","&B1&","&""""&C1&""""

 

[Westbury]

Thanks. This formula works but how do I translate it into VBA?

 

[Fluff]

How about

   Range("C1") = Range("A1") & "," & Range("B1") & ",""" & Range("C1") & Chr(34)

 

[Fluff]

If you are trying to do it on an entire column try

Sub Westbury()
   Dim Cl As Range
   Dim x As Variant
   For Each Cl In Range("D2", Range("C" & Rows.Count).End(xlUp).Offset(, 1))
      x = join(Application.Index(Cl.Offset(, -3).Resize(, 2).Value, 1, 0), ",")
      Cl = x & ",""" & Cl.Offset(, -1) & Chr(34)
   Next Cl
End Sub

 

[Westbury]

Thanks, this works.

Last question on this(!) How could I put the output on a new worksheet in the same file?

 

[Fluff]

Which of the two codes are you referring to?

 

[Fluff]

Assuming that you are talking about the loop, try

Sub Westbury()
   Dim Ary As Variant, x As Variant
   Dim i As Long
   With Sheets("Sheet1")
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp).Offset(, 3)).Value2
   End With
   For i = 1 To UBound(Ary)
      x = join(Application.Index(Ary, i, Array(1, 2)), ",")
      Sheets("Sheet2").Range("A" & i) = x & ",""" & Ary(i, 3) & Chr(34)
   Next i
End Sub

 

[Rick Rothstein]

How about

   Range("C1") = Range("A1") & "," & Range("B1") & ",""" & Range("C1") & Chr(34)

Given you used quotes everywhere else, why use Chr(34) at the end (replacing it as below saves 3 characters)?

Range("C1") = Range("A1") & "," & Range("B1") & ",""" & Range("C1") & """"

 

[Fluff]

Given you used quotes everywhere else, why use Chr(34) at the end (replacing it as below saves 3 characters)?

Absolutely no idea
I'll blame it on the fact that it's been a long day.