• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help w/ macro to find lane combinations & transit time

LearningVB

New Member
I have a data set with Origin, Destination, and transit times in 3 separate columns. I need to use this data to find all the possible lane combinations and sum the transit times.

Im a VB novice so somehow figured out a way to find the various combinations but i get a whole list of various combinations

The challenge is that it should provide me a combination of only those lanes which are some how connected i.e. destination on one lane should be the same as origin in the next lane and on ...

Please help !

For e.g.
My Data is in this format
----------------------------------------------
Origin Destination Transit Time
A B 5
B C 10
X Y 8
E F 4
Y E 1
Im hoping for results in a format similar to this and could be a string from J-K-L-M-Y in case its finds the connections
----------------------------------------------
Combinations
A-B 5
A-B-C (or A-C) 15
X-Y 8
X-Y-E (or X-E) 9
E-F 4
Y-E 1

The code i have currently used is
---------------------------------------------
Code:
Option Explicit

Sub Sample()
    Dim i As Long, j As Long, k As Long
    Dim CountComb As Long, lastrow As Long
   
    Range("G2").Value = Now

    Application.ScreenUpdating = False

    CountComb = 0: lastrow = 6
   
    For i = 1 To 2: For j = 1 To 2
        Range("G" & lastrow).Value = Range("A" & i).Value & "-" & _
                                     Range("B" & j).Value
        lastrow = lastrow + 1
        CountComb = CountComb + 1
    Next: Next
       
    Range("G1").Value = CountComb
    Range("G3").Value = Now

    Application.ScreenUpdating = True
End Sub
 
Back
Top