Concatenate two cells

S

shyamsundar75

New member
Joined
Feb 21, 2013
Messages
5
Reaction score
0
Points
0
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
 
Yes, something like below
Code: 
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
 
no errors this side. Can add the .value2 to the end of the while

Code: 
Sub test()
Const sDelim As String = [COLOR=#ff0000]""[/COLOR]  'what do you want can be nothing
Dim i As Long


i = 2 'presume u skip the header
While ActiveSheet.Cells(i, 3)[COLOR=#ff0000].Value2[/COLOR] <> ""
  ActiveSheet.Cells(i, 8).Value2 = ActiveSheet.Cells(i, 3).Value2 & sDelim & ActiveSheet.Cells(i, 4).Value2
  i = i + 1
Wend
End Sub
 
@WOO

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

In this case the use of currentregion is obvious.

Another method:

Code: 
[H2:H20000]=[if(C2:C20000="","",C2:C20000&" "&D2:D20000)]
 
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.
 
@WOO

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

Code: 
[sheet3!H2:H20000]=[if(sheet1!C2:C20000="","",sheet1!C2:C20000&" "&sheet2!D2:D20000)]
 
Last edited:
You must log in or register to reply here.
Back
Top