Concatenate in VBA

[murthysri272]

Hi,

Could you please help me code for below scenario:

I have 3 columns (E, I, M) in Sheet 2 . I would like to concatenate these 3 columns and place in sheet 1 of Column H.


Thanks and regards,
Sri

[Bob Phillips]

With Worksheets("Sheet2")

For i = 2 To .Range("E2").End(xlDown).Row

j = j + 1
Worksheets("Sheet1").Cells(j, "H").Value = .Cells(i, "E").Value & " " & _
.Cells(i, "I").Value & " " & _
.Cells(i, "M").Value
Next i
End With

[murthysri272]

Hi,
It working fine. But if My result should come from H20, how can i achieve it?


Thank you,


Regards,
Sri

[murthysri272]

Hi,
Thanks for your help.

I got my desired result now with below code.



Sub Concat()
With Worksheets("Conversion Table")
For i = 5 To .Range("E2").End(xlDown).Row
j = j + 1
Worksheets("HYPERION").Range("I20:I3000")(j).Value = .Cells(i, "I").Value & "" & _
.Cells(i, "M").Value & "" & _
.Cells(i, "E").Value

Next i
End With
End Sub



Thanks a lot for your help!

Thanks again.



Regards,
Sri

[SamT]

Worksheets("HYPERION").Range("I20:I3000")(j)
Worksheets("HYPERION").Cells(1 + 15, "I")