Results 1 to 7 of 7

Thread: VBA Counting instances of journeys

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    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.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •