Concatenate two cells

[shyamsundar75]

Hi Friends,

I need to concatenate the names in the colums C and D and the values needs to be stored in Column H.
Is it possible to create infinite FOR loop. Once the there is a blank row the loop should exit.

Regards
Shyam

[WizzardOfOz]

Yes, something like below

const sDelim as string = " "  'what do you want can be nothing
dim i as long

i = 2 'presume u skip the header
while activesheet.cells(i,3) <> ""
  activesheet.cells(i,8).value2 = activesheet.cells(i,3).value2 & sDelim  & activesheet.cells(i,4).value2
  i = i + 1
wend

[shyamsundar75]

Thans Wizzard but its not working throwing an error msg.

[WizzardOfOz]

no errors this side. Can add the .value2 to the end of the while

Sub test()
Const sDelim As String = ""  'what do you want can be nothing
Dim i As Long


i = 2 'presume u skip the header
While ActiveSheet.Cells(i, 3).Value2 <> ""
  ActiveSheet.Cells(i, 8).Value2 = ActiveSheet.Cells(i, 3).Value2 & sDelim & ActiveSheet.Cells(i, 4).Value2
  i = i + 1
Wend
End Sub

[shyamsundar75]

Thanks Wizzard

[snb]

@WOO

While ... Wend in VBA has been replaced by Do ... Loop

In this case the use of currentregion is obvious.

Another method:

[H2:H20000]=[if(C2:C20000="","",C2:C20000&" "&D2:D20000)]

[WizzardOfOz]

Yes Snb, much more elegant, u taught me something. Thanks. What's the syntax if you need to add a sheet name(s) to that e.g if H on sheet1, c on sheet2 and d on sheet3?

Checked and seen that 2013 has dropped the while/wend. However still works in 2010. Presumably this is because the do loop allows "exit do". How are old dogs going to learn new tricks.

[snb]

@WOO

While ... Wend has been replaced in Office 2000
Do is more flexible: using Do While, Do Until, Loop While, Loop Until & Exit Do

[sheet3!H2:H20000]=[if(sheet1!C2:C20000="","",sheet1!C2:C20000&" "&sheet2!D2:D20000)]