VBA concatenate two ranges

0
votes

I'm trying to concatenate two ranges in excel using VBA so each cell in range1 concatenates all cells in range2 until cell A is null. Please see below:

Range1(column A): Range2(column B):
50703, 50702      52797, 52848

Concatenate(column C):
50703-52797, 50703-52848, 50702-52797, 50702-52848
3
excelvba
Were you going to show your own effort toward a solution? - user4039065
It looks like you are trying to find all permutations of both ranges of cells using column A cell values as the first part of the results. What is the total number of cells used in the A and B columns? - 5202456
What do you plan to use the value of column C for after you've got that result? - jsheeran
jsheeran - I have a list of 54 values in column A and a list of 59 values in column B which will take a while to manually concatenate (3186 values). The data will be loaded into a different system. - BigBang83
Jeeped - I don't have any code written yet for this. Did some research online but couldn't find anything apart from concatenating two ranges which (line by line) is not what I'm trying to do here. What I want is for each cell in range1 concatenate with all cells in range2, then move to the next cell until cell in range1 is null. - BigBang83

3 Answers

1
votes

This will insert all the combinations of values in column A and B into column C and concatenate them with a hyphen: 

Sub combinations()

    Dim i As Long, j As Long, n As Long
    Dim valsColA As Variant, valsColB As Variant

    With ThisWorkbook.Sheets("Combinations") ' change sheet name, if necessary

        valsColA = .Range(.Cells(1, 1), .Range("A1").End(xlDown)).Value
        valsColB = .Range(.Cells(1, 2), .Range("B1").End(xlDown)).Value

        For i = LBound(valsColA) To UBound(valsColA)
            For j = LBound(valsColB) To UBound(valsColB)
                n = n + 1
                .Cells(n, 3).Value = valsColA(i, 1) & "-" & valsColB(j, 1)
            Next j
        Next i

    End With
End Sub

enter image description here

0
votes

Here is what I came up with, although @Miqi180 got there first:

Sub ABPerm()

Dim Acol As Integer
Dim Bcol As Integer
Dim RowNumA As Integer
Dim RowNumB As Integer
Dim RowNumC As Integer

Acol = Range("A" & Rows.Count).End(xlUp).Row
Bcol = Range("B" & Rows.Count).End(xlUp).Row

RowNumA = 1
RowNumB = 1
RowNumC = 1

For a = 1 To Acol

    For b = 1 To Bcol
        Range("C" & RowNumC).Value = Range("A" & RowNumA).Value & "-" & Range("B" & RowNumB).Value
        RowNumB = RowNumB + 1
        RowNumC = RowNumC + 1
    Next b

    RowNumB = 1
    RowNumA = RowNumA + 1

Next a

End Sub
0
votes

Try this code

Sub Test()
Dim rng         As Range

Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
rng.Offset(, 2).Value = Evaluate("If(Row(1:" & rng.Rows.Count & ")," & rng.Address(, , , True) & " & " & "-" & rng.Offset(, 1).Address(, , , True) & ")")
End Sub