VBA Name combinations

[j_jungy]

I have a list of firstnames from A1 to A5 and a list of last names from B2 to B5. I need to create a nested loop that will output the different combinations of first names and last names on any of the cells within the sheet. Could someone help?

Here is the list of the first names on the left column and last names in the right column.

Penelope	Jill
Gwyneth	Diane
Alice	Heidi
Anna	Lilly
Aurora	Victoria

<tbody>
</tbody>

 

[Max1616]

Try this!

Sub Names()

Dim lrow1 As Long
Dim lrow2 As Long
Dim i As Long
Dim j As Long
Dim Counter As Long

lrow1 = Cells(Rows.Count, 1).End(xlUp).Row
lrow2 = Cells(Rows.Count, 2).End(xlUp).Row
Counter = 1

For i = 1 To lrow1
    For j = 1 To lrow2
        Cells(Counter, 4) = Cells(i, 1) & " " & Cells(j, 2)
        Counter = Counter + 1
    Next j
Next i

End Sub

 

[Max1616]

This code assumes your names live in column A & B. This code will work for any number of names in these two columns. The output will got to column D (4)

 

[j_jungy]

This code assumes your names live in column A & B. This code will work for any number of names in these two columns. The output will got to column D (4)

Thank you so much!!

Is there a code specifically for the five rows I have rather than a code that works for any number of names?

 

[Max1616]

Sure, try this:

Sub Names()

Dim lrow1 As Long
Dim lrow2 As Long
Dim i As Long
Dim j As Long
Dim Counter As Long

lrow1 = 5
lrow2 = 5
Counter = 1

For i = 1 To lrow1
    For j = 1 To lrow2
        Cells(Counter, 4) = Cells(i, 1) & " " & Cells(j, 2)
        Counter = Counter + 1
    Next j
Next i

End Sub

 

[hiker95]

I have a list of firstnames from A1 to A5 and a list of last names from B2 to B5.

With your original flat text display in your reply #1:

j_jungy,

Try:

Sub NamesV2()
' hiker95, 11/28/2017, ME1033202
Dim lrow1 As Long
Dim lrow2 As Long
Dim i As Long
Dim j As Long
Dim Counter As Long
lrow1 = 5
lrow2 = 5
Counter = 1
For i = 1 To lrow1
  For j = 2 To lrow2
    Cells(Counter, 4) = Cells(i, 1) & " " & Cells(j, 2)
    Counter = Counter + 1
  Next j
Next i
Columns("D").AutoFit
End Sub

 

[hiker95]

j_jungy,

Welcome to the MrExcel forum.

It would really help if you could provide another flat text display of what your results should be.

 

[hiker95]

I have a list of firstnames from A1 to A5 and a list of last names from B2 to B5.

j_jungy,

Here is a macro solution for you to consider that uses range objects in A1 to A5, and, in B2 to B5, to create an array that will combine the first and last names, and, output the array results into column D, to range D1:D20.

Sub CombineNames()
' hiker95, 11/28/2017, ME1033202
Dim a As Range, b As Range
Dim lra As Long, lrb As Long
Dim o As Variant, j As Long
With ActiveSheet
  .Columns(4).ClearContents
  lra = .Cells(.Rows.Count, 1).End(xlUp).Row
  lrb = .Cells(.Rows.Count, 2).End(xlUp).Row
  ReDim o(1 To lra * lrb)
  For Each a In .Range("A1:A" & lra)
    For Each b In .Range("B2:B" & lrb)
      j = j + 1
      o(j) = a.Value & " " & b.Value
    Next b
  Next a
  .Range("D1").Resize(UBound(o)) = Application.Transpose(o)
  .Columns(4).AutoFit
End With
End Sub