Results 1 to 3 of 3

Thread: Combing Cells

  1. #1
    Seeker alan.sluder's Avatar
    Join Date
    Nov 2014
    Posts
    5
    Articles
    0
    Excel Version
    Excel 2013

    Combing Cells



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

    Need help with VBA code to combine/concatenate values in cells A1, A2 and A3 with a range of values in column B. I can do a loop to do A1 with B1 and then repeat till get to the last row in column B and store the combined results in a new column (ie C). I would like to do the following looping

    A1mergeB1 to C1
    A2mergeB1 to C2
    A3mergeB1 to C3
    A1mergeB2 to C4
    A2mergeB2 to C5
    A3mergeB2 to C6

    Etc til you get to the last row.

  2. #2
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    43
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    Try this

    Code:
    Sub test()
    Dim i As Integer, j As Integer, dest_row As Integer
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    
    
    Set rng1 = Range("A1:A3")
    Set rng2 = Range(Range("B1"), Range("B1").End(xlDown))
    Set rng3 = Range("C1")
    
    
    For i = 1 To rng2.Rows.Count
        For j = 1 To rng1.Rows.Count
            dest_row = rng1.Rows.Count * (i - 1) + j
            rng3.Offset(dest_row - 1) = rng1.Cells(j) & rng2.Cells(i)
        Next j
    Next i
    
    
    End Sub

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,691
    Articles
    0
    Excel Version
    365
    Perhaps a little easier to read:
    Code:
    Sub blah()
    Set Destn = Range("C1")
    For Each cll In Range(Range("B1"), Range("B1").End(xlDown))
      For Each celle In Range("A1:A3")
        Destn.Value = celle.Value & cll.Value
        Set Destn = Destn.Offset(1)
      Next celle
    Next cll
    End Sub

Posting Permissions

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