VBA Counting instances of journeys

[Ronanm]

Hi

I'm trying 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...

23381

[大灰狼1976]

Hi Ronanm!
You can do it with a commandbutton used following code:

Private Sub CommandButton1_Click()
Dim arr, i&, j&, s$, arr1
arr = [a1].CurrentRegion
For i = 1 To UBound(arr)
If Left(arr(i, 1), 3) = "Qtr" Then
s = s & ",/"
Else
s = s & "," & arr(i, 1)
End If
Next i
s = s & ","
arr = Split(s, "/")
arr1 = Range("d2:f" & [d65536].End(3).Row)
For i = 1 To UBound(arr1)
For j = 1 To UBound(arr)
If InStr(arr(j), "," & arr1(i, 1) & ",") > 0 And InStr(arr(j), "," & arr1(i, 2) & ",") > 0 Then n = n + 1
Next j
arr1(i, 3) = n: n = 0
Next i
[f2].Resize(UBound(arr1)) = Application.Index(arr1, , 3)
End Sub


Or you can do it with custom function also.
Please refer to the attached.

[Ronanm]

Wow. Thank you so much for a truly "complete" answer.

Life saver
:friends:

[Ronanm]

Hi


Actually sorry, I was premature with the response (as I was on holiday at the time)

For example it misses one Cardiff Devon in Qtr2.Can't see why?

Thank you

[Ronanm]

Hi

Actually sorry, I was premature with the response (as I was on holiday at the time)

For example it misses one Cardiff Devon in Qtr2.Can't see why?

Thank you

[大灰狼1976]

I have no problem running here.
Maybe it's your "Cardiff" writing error like you posted picture's range("A2").
If there is an attachment, I will find out the problem right away.

[大灰狼1976]

by the way, I added the realtime refresh statement: "Application.Volatile 'realtime refresh"
Please refer to the attached.