using row range to dim array, and use array to ite

Question

Please look at my sample data and code to understand what I'm trying to do.

I need to use the value of Cells(, 3) to define a range to populate a Trialnumber(18) array. I need the array to iterate through a For loop, to count filled cells in column H for each trial and print the count to column T in the last row of each trial. I will also need the array for further data analysis in future(Unless someone can come up with a better solution).

At the moment I am experimenting with 3 modules of code, trying to get the desired solution.

Module 2 is the only one with no errors, and prints the value in the right cell, but it is printing the total filled cell count (562), rather than per trial (expected value = 1 or 2).

Module 1 is as follows:

Sub dotcountanalysis()
Dim startpoint As Long
startpoint = 1
Dim lastrow As Long
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
Dim i As Long

With Worksheets("full test")

For i = 1 To 18
      For n = startpoint To lastrow + 1

        If Cells(n, 3).Value <> "Trial, " & CStr(i) Then
           Dim nMinusOne As Long
           nMinusOne = n - 1
           Dim trialCount As Long
           'Set Trialnumber(i-1) = Range(cells(startpoint, 3), cells(n-1, 3))
           trialCount = Application.WorksheetFunction.CountA(Range("H" & CStr(startpoint) & ":" & "H" & CStr(nMinusOne)))
           Range("T" & CStr(startpoint) & ":" & "T" & CStr(nMinusOne)).Value = trialCount
           startpoint = n
           Exit For
         End If

        Next n
Next i
End With
End Sub

It returns a "method _range of object _global falied" error on line: trialCount = Application.WorksheetFunction.CountA(Range("H" & CStr(startpoint) & ":" & "H" & CStr(nMinusOne)))

Module 3 is as follows:

Sub dotcountanalysis3()

Dim pressedCount As Long
Dim myCell As Range
Dim pressedRange As Range

'create trials array
Dim t(18) As Range

'set range for trialnumber (t)

Dim startpoint As Long
startpoint = 1
Dim lastrow As Long
lastrow = Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 To 18
      For n = startpoint To lastrow
      startpoint = 7
        If Cells(n, 3).Value <> "Trial, " & CStr(i) Then
           Set t(i - 1) = Range(Cells(startpoint, 3), Cells(n, 3))
           n = n + 1
           startpoint = n
           Exit For
         End If

        Next n
Next i

'count presses in each trial

With Worksheets("full test")
    For i = 0 To 17
    pressedCount = Application.WorksheetFunction.CountA _
    (.Range(.Cells(t(), "H"), .Cells(.Rows.Count, "H")))
    If pressedCount = 0 Then Exit Sub
    'make sure there are cells or else the next line will fail
    Set pressedRange = .Columns("H").SpecialCells(xlCellTypeConstants)

        For Each myCell In pressedRange.Cells
    'only loop through the cells containing something
        .Cells(myCell.Row, "T").Value = pressedCount
        Next myCell
    Next i
End With

End Sub

It returns a run-time "type mismatch" error on line: pressedCount = Application.WorksheetFunction.CountA _ (.Range(.Cells(t(), "H"), .Cells(.Rows.Count, "H")))

Edit: I have updated code in mod 3 and updated error.

Answer 1

When counting things I like to use a dictionary object, and arrays are faster than going row by row on the sheet.

This will count unique combinations of Block+Trial: to count only by trial you would just use k = d(r, COL_TRIAL)

Dim dBT As Object 'global dictionary

Sub dotcountanalysis()

    'constants for column positions
    Const COL_BLOCK As Long = 1
    Const COL_TRIAL As Long = 2
    Const COL_ACT As Long = 7

    Dim rng As Range, lastrow As Long, sht As Worksheet
    Dim d, r As Long, k, resBT()

    Set sht = Worksheets("full test")
    lastrow = Cells(Rows.Count, 3).End(xlUp).Row
    Set dBT = CreateObject("scripting.dictionary")

    Set rng = sht.Range("B7:H" & lastrow)

    d = rng.Value  'get the data into an array

    ReDim resBT(1 To UBound(d), 1 To 1) 'resize the array which will
                                        '  be placed in ColT

    'get unique combinations of Block and Trial and counts for each
    For r = 1 To UBound(d, 1)
        k = d(r, COL_BLOCK) & "|" & d(r, COL_TRIAL) 'create key
        dBT(k) = dBT(k) + IIf(d(r, COL_ACT) <> "", 1, 0)
    Next r

    'populate array with appropriate counts for each row
    For r = 1 To UBound(d, 1)
        k = d(r, 1) & "|" & d(r, 2)   'create key
        resBT(r, 1) = dBT(k)          'get the count
    Next r

    'place array to sheet
    sht.Range("T7").Resize(UBound(resBT, 1), 1) = resBT

    'show the counts in the Immediate pane (for debugging)
    For Each k In dBT
        Debug.Print k, dBT(k)
    Next k


End Sub