concatenate in vba

[lezawang]

Hi
I am trying to concatenate the first 10 cells in A, and B columns and put them in C. So I did this program which did not work. Any help would be very much apprecaited.

Sub concat()
Dim i As Integer, j As Integer
For i = 1 To 10
For j = 1 To 10
Cells(i, j + 2).Value = Cells(i, j).Value & Cells(i, j + 1).Value
Next i
Next j
End Sub

 

[Fluff]

Are you trying to concatenate A1 & B1 & put the result in C1?
If not please clearly explain what you want, rather than posting non-working code & rely on us to guess.

 

[sheetspread]

You only need one loop here:

Sub concat()
Dim i As Integer
For i = 1 To 10
Cells(i, 3).Value = Cells(i, 1).Value & Cells(i, 2).Value
Next
End Sub

 

[sheetspread]

Not as proper, but a one line version:

Sub concat()
Range("c1").Resize(10).Value = Evaluate(Range("a1").Resize(10).Address & "&" & Range("b1").Resize(10).Address)
End Sub

 

[lezawang]

Yes exactly. Thank you.

Are you trying to concatenate A1 & B1 & put the result in C1?
If not please clearly explain what you want, rather than posting non-working code & rely on us to guess.

 

[Fluff]

Have you tried either of the codes supplied by sheetspread?
Just for the sake of it, here's another option

Sub myConcat()
   Dim Cl As Range
   For Each Cl In Range("A1:A10")
   Cl.Offset(, 2).Value = Join(Application.Index(Cl.Resize(, 2).Value, 1, 0), "")
   Next Cl
End Sub

 

[lezawang]

Thank you all for the help. I need more time to understand the code and many things are new to me i.e Join, Index, offset.. I thought it is easier than this. I think I am not ready for it now. I will try to understand your code. Thank you so much once again

 

[Fluff]

Glad we could help & thanks for the feedback

 

[lezawang]

I just saw this one. I thought that was my code. Now I understand it perfectly.
Thank you so much to all of you.

You only need one loop here:

Sub concat()
Dim i As Integer
For i = 1 To 10
Cells(i, 3).Value = Cells(i, 1).Value & Cells(i, 2).Value
Next
End Sub

 

[sheetspread]

anytime