Trace all possible paths & Duration as per the Directed Graph (1 Viewer)

[Futurz]

Hi, everyone ,
i am looking for a VBA Code for excel worksheet that finds "All Possible paths & Duration as per the Directed Graph"
Given Data in Cells:
A2 to C7
E5 & F5

Result in Cells:
E8 to F11
The Model is shown in attached image.

 

[arnelgp]

you need to modify for you need.

 

[Futurz]

Dear arnelgp, thanks for your reply,
Actually I want to trace all possible paths (Not only the shortest path)
Start from source to Destination

 

[CJ_London]

You’ll need some nested loops or a recursive function. Which depends on wether the requirement is always a-z or could be a-c for example plus whether or not in another example some paths tested do not lead to z

a-c for example would be
AC
ABC

 

[arnelgp]

Dim c_path As Collection
Dim c_value As Collection

Private Sub do_path()
    Dim i As Integer
    Dim a As String, b As String
    Dim v As Integer
    Set c_path = New Collection
    Set c_value = New Collection
   
    For i = 2 To 8
        a = Range("A" & i)
        If a = "A" Then
            b = Range("B" & i)
            v = Range("C" & i)
           
            Call do_sub(i + i, 8, a & b, b, v)
        End If
    Next
   
    For i = 1 To c_path.Count
        Debug.Print c_path(i), c_value(i)
    Next

End Sub

Private Sub do_sub( _
                        ByVal i_start As Integer, _
                        ByVal i_end As Integer, _
                        ByVal p As String, _
                        ByVal f As String, _
                        ByVal v As Integer)
    Dim j As Integer, x As String
    For j = i_start To i_end
        If Range("A" & j) = f Then
            x = Range("B" & j)
            v = v + Range("C" & j)

            If x <> "Z" Then

                Call do_sub(j + 1, 8, p & x, x, v)
                v = v - Range("C" & j)
            Else
                c_path.Add p & x
                c_value.Add v
            End If
        End If
    Next
End Sub

End Sub

 

[Futurz]

You’ll need some nested loops or a recursive function. Which depends on wether the requirement is always a-z or could be a-c for example plus whether or not in another example some paths tested do not lead to z

a-c for example would be
AC
ABC

dear cj_london,
the requirement is not always "a to z" it should be changeable to any given Source & destination for example:
A to C
A->C
A->B->C
(please need a Separator -> )
Thanks

 

[Futurz]

Dim c_path As Collection
Dim c_value As Collection

Private Sub do_path()
    Dim i As Integer
    Dim a As String, b As String
    Dim v As Integer
    Set c_path = New Collection
    Set c_value = New Collection
  
    For i = 2 To 8
        a = Range("A" & i)
        If a = "A" Then
            b = Range("B" & i)
            v = Range("C" & i)
          
            Call do_sub(i + i, 8, a & b, b, v)
        End If
    Next
  
    For i = 1 To c_path.Count
        Debug.Print c_path(i), c_value(i)
    Next

End Sub

Private Sub do_sub( _
                        ByVal i_start As Integer, _
                        ByVal i_end As Integer, _
                        ByVal p As String, _
                        ByVal f As String, _
                        ByVal v As Integer)
    Dim j As Integer, x As String
    For j = i_start To i_end
        If Range("A" & j) = f Then
            x = Range("B" & j)
            v = v + Range("C" & j)

            If x <> "Z" Then

                Call do_sub(j + 1, 8, p & x, x, v)
                v = v - Range("C" & j)
            Else
                c_path.Add p & x
                c_value.Add v
            End If
        End If
    Next
End Sub

End Sub

Dear arnelgp,
i inserted the above code, but its not giving any result..

 

[arnelgp]

here is a sample xlsm.
Enable the macro.
goto vba and click anywhere inside do_path sub.
press F5 (run)
go back to the sheet and see the result.

 

[Futurz]

here is a sample xlsm.
Enable the macro.
goto vba and click anywhere inside do_path sub.
press F5 (run)
go back to the sheet and see the result.

Dear arnelgp,
Thanks and appreciate you efforts, the attached file with vba code is working good for Source "A" & Destination "Z"
But when I changed destination to "C". The result should be as shown in image below respectively all possible paths... (Separated by any symbol please)

 

[arnelgp]

no need to go to VBA.
you type the Source and Destination on the "yellow" cell.
press the blue shape.

 

[Futurz]

no need to go to VBA.
you type the Source and Destination on the "yellow" cell.
press the blue shape.

Dear arnelgp,
Your code working "perfect" but Limited to the given data only. But when given Data or Path Network modified or customized its not giving correct results.
I appreciate your effort
The concept of solution should always follow all possible paths all listed Sources & Destinations (Column A) & (Column B) respectively

 

[MajP]

Route Planning - shortest path

I'm trying to get my head around using the following data to create the shortest route between 2 places in a map using VBA in either excel or access. ID Name Connection 1 Connection 2 1 Location1 2 2 Location2 3 4 3 Location3 2 4 Location4 2 5 5 Location5 3 5 So...

www.access-programmers.co.uk

 

[arnelgp]

ok, i adjusted the code.
but don't leave a gap between rows.

 

[Futurz]

Dear arnelgp,
Its great, All paths are being listed properly by using your vba code.
But the Adding of duration is having mistake in two cells (please see the attached file)

Lastly I just request to please Add Symbol to separate the Results as shown in the attached file

Thanks a lot...

 

[arnelgp]

added symbol fixed the calc.

 

[Futurz]

added symbol fixed the calc.

Dear arnelgp,

***Well done, I have cross checked it. Everything is working fine***

I will be having some more than 100 rows, hence I need to assign the different combination of alphabets & numerical figures (example "CUU-01" instead of using single alphabets "A,B,C,D...")

These combination of figures is not working with the code...(please see the attached file)

 

[Futurz]

Dear arnelgp,
Further, one path is missing :

 

[Isaac]

@Futurz you have a tough professor!

 

[Futurz]

Dear arnelgp,
Further, more

path is missing :

 

[The_Doc_Man]

This is obviously someone giving you a problem having to do with topological sorting. Typically this has to be done using recursion to get ALL possible paths correctly managed. Unfortunately, Access doesn't do recursive queries (though some other SQL engines CAN do that.) You really need code and/or a multipass algorithm to do such things properly.