VBA Counting instances of journeys

[Ronanm]

Hi

I need to use VBA to count how many times a journey occurs in each Quarter. I have a table which shows the Journeys to be counted (in either direction) D:E, along with the various journeys per Quarter in column A.

So in the example below. the journey London to Cardiff (either direction) appears 2 times in Column A (Qtr2 and Qtr4), Cardiff to Devon (either direction) appears 3 times etc...

[/URL][/IMG]

Thanks

 

[onlyadrafter]

Hello,

This gives the results you have provided.

Sub COUNT_JOURNEY()
    For MY_JOURNEY = 2 To Range("D" & Rows.Count).End(xlUp).Row
        MY_A = Range("D" & MY_JOURNEY).Value
        MY_B = Range("E" & MY_JOURNEY).Value
        For MY_DATA = 2 To Range("A" & Rows.Count).End(xlUp).Row
            For MY_NEXT_DATA = MY_DATA + 1 To Range("A" & Rows.Count).End(xlUp).Row
                If Left(Range("A" & MY_NEXT_DATA).Value, 3) = "Qtr" Then GoTo MY_CONT
                If Range("A" & MY_DATA).Value = MY_A Or Range("A" & MY_DATA).Value = MY_B Then
                    If Range("A" & MY_NEXT_DATA).Value = MY_A Or Range("A" & MY_NEXT_DATA).Value = MY_B Then
                        MY_COUNT = MY_COUNT + 1
                    End If
                End If
            Next MY_NEXT_DATA
MY_CONT:
        Next MY_DATA
            Range("F" & MY_JOURNEY).Value = MY_COUNT
            MY_COUNT = 0
    Next MY_JOURNEY
End Sub

Does it work for all journeys?

 

[Ronanm]

That works a treat (Nearly got caught out by "my" mis-spelling of one of the "Cardiff"s!), thank you so much