Results 1 to 8 of 8

Thread: Concatenate two cells

  1. #1

    Concatenate two cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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

  3. #3
    Thans Wizzard but its not working throwing an error msg.

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    no errors this side. Can add the .value2 to the end of the while

    Code:
    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

  5. #5
    Thanks Wizzard

  6. #6
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    @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)]

  7. #7
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    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.

  8. #8
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    374
    Articles
    0
    Excel Version
    2020
    @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 by snb; 2014-11-21 at 12:04 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •