Looping through all possible combinations of two ranges

[waimea]

I have "two" ranges, (A2:A41) with identical entries and I want to loop through all possible combinations of (A2:A41) and (A2:A41).

I am trying to use two for loops to get 1600 variations for these ranges but without success.



Sub InnerAndOuterLoop()

For K = 0 to 39

For L = 0 to 39

Next L
Next K




Sub ForEachLoops()

Dim city1 As Variant
Dim city2 As Variant


For Each city1 In Range("myRange")
Debug.Print city1 & " " & city2

For Each city2 In Range("myRange")
Debug.Print city2 & " " & city1

Next city2


Next city1


End Sub


This code works like I want it to. How can I use the for loop to get the same results?

[Paul_Hossler]

Option Explicit

Sub InnerAndOuterLoop()
Dim K As Long, L As Long, O As Long

O = 1

With ActiveSheet
For K = 0 To 39
For L = 0 To 39
.Cells(O, 3).Value = .Cells(K + 2, 1).Value & " -" & .Cells(L + 2, 1).Value
O = O + 1
Next L
Next K
End With
End Sub

[waimea]

Thank you for your reply Paul! I learned something right now!

[waimea]

Sub Test()


Dim city As Variant
Dim city1 As Variant
Dim city2 As Variant

Dim result, result2 As Variant
Dim arrCity As Variant
Dim WkArr As Variant
Dim i As Long
Dim j As Long
Dim R As Long


i = 1


ReDim WkArr(1 To 40 * 40, 1 To 4)


'arrCity = Sheets("Test2").Range("Range")
'
'For Each city In arrCity
' Debug.Print city
'Next


For Each city1 In Range("Range")
'Debug.Print city1 & " " & city2

For Each city2 In Range("Range")
'Debug.Print city2 & " " & city1

On Error Resume Next
WkArr(i, 1) = city1 & " " & city2
WkArr(i, 2) = city2 & " " & city1

WkArr(i, 3) = Application.WorksheetFunction.VLookup(city1, Sheets("Data").Range("A1:PY305"), 314, False)
WkArr(i, 4) = Application.WorksheetFunction.VLookup(city2, Sheets("Data").Range("A1:PY305"), 314, False)

i = i + 1

Next city2

Next city1

With Sheets("Report")

Dim lRow As Long
Dim lCol As Long

'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find the last non-blank cell in row 1
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

.Cells(1, 1).Offset(lRow, lCol) = WkArr(i, 1)
.Cells(1, 2).Offset(lRow, lCol) = WkArr(i, 2)
.Cells(1, 3).Offset(lRow, lCol) = WkArr(i, 3)
.Cells(1, 4).Offset(lRow, lCol) = WkArr(i, 4)
.Cells(1, 5).Offset(lRow, lCol) = WkArr(i, 5)

End With



R = Cells(Rows.Count, 1).End(xlUp).Row + 1
With Sheets("Test4")
.Cells(R, 1).Resize(UBound(WkArr, 1), UBound(WkArr, 2)) = WkArr
End With


End Sub




I am trying to create a new block of data for each city with the offset function and where the first entry is in cell A1 and then the next loop should be in Cell A2 etc.

Can anyone help me with this?

With Sheets("Report")


Dim lRow As Long
Dim lCol As Long

'Find the last non-blank cell in column A(1)
lRow = Cells(Rows.Count, 1).End(xlUp).Row

'Find the last non-blank cell in row 1
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

.Cells(1, 1).Offset(lRow, lCol) = WkArr(i, 1)
.Cells(1, 2).Offset(lRow, lCol) = WkArr(i, 2)
.Cells(1, 3).Offset(lRow, lCol) = WkArr(i, 3)
.Cells(1, 4).Offset(lRow, lCol) = WkArr(i, 4)
.Cells(1, 5).Offset(lRow, lCol) = WkArr(i, 5)

End With

This is the part of the code that is not working.

For each loop I would like the data to start in A1, the next loop in C1, the next loop in E1.

Then if possible continue with A25, C25 and E25?

[Paul_Hossler]

got an example of the before and after?

[waimea]

I don't have access to the workbook right now but:

Desired output:

"A1" State1
"A2" City1
"A3" State2
"A4" City2
"A5" Name1
"A6" Name2

"C1" State1
"C2" City1
"C3" State2
"C4" City2
"C5" Name1
"C6" Name2

"E1" State1
"E2" City1
"E3" State2
"E4" City2
"E5" Name1
"E6" Name2

"A8" State1
"A9 City1
"A10" State2
"A11" City2
"A12" Name1
"A13" Name2

"C8"...

etc



Current output:
"A1" State1
"B1" City1
"C1" State2
"D1" City2
"E1" Name1
"F1" Name2

[waimea]

Any suggestions? I would really want to be able to move on with my code.

[Paul_Hossler]

I suggest that you attach a workbook showing the Before and the After with sampledata

It's just too hard to visualize from a description