Hello,
I am having an issue with creating a macro to find all the combinations possible without repetition.
I have attached a sample of what my worksheet looks like and the macro that I managed to do but doesn't work correctly.
Sub ToAndFrom()
Dim lastRow As Long
Dim i As Long
Dim j As Long
Dim n As Long
'Application.ScreenUpdating = False
'Application.EnableEvents = False
'Application.Calculation = xlCalculationManual
lastRow = ActiveSheet.Range("N" & Rows.Count).End(xlUp).Row
For n = 7 To lastRow
For i = n To lastRow
For j = i + 1 To lastRow
Cells(i, "B").Value = Cells(n, "N").Value
Cells(i, "C").Value = Cells(j, "N").Value
i = i + 1
Next j
Next i
Next n
End Sub
What I need for the code to do is this:
I have a row ( "N" ) that has all the city names one under another, starting from position N7 and let's say it has these name: A B C D E
In rows B and C, I need the macro to do this:
Row B Row C
A B
A C
A D
A E
B C
B D
B E
C D
C E
D E
I need all the combinations, without a repetition ( AB and BA have the same distance between them so it's pointless to have bouth)
The VBA code that I wrote works perfectly for the first set ( AB / AC / AD / AE) but then for the next rows, it starts to overwrite the ones before so I think I need a limiter or another row count or something similar to that but I don't know where and how to modify it
Please help
Bookmarks