Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Dim dBT As Object 'global dictionary
- Sub buttonpresscount()
- 'constants for column positions
- Const COL_BLOCK As Long = 1
- Const COL_TRIAL As Long = 2
- Const COL_ACT As Long = 7
- Const COL_AOI As Long = 8
- 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:I" & 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 pressedcounts 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
- 'clear dictionary
- dBT.RemoveAll
- 'count AOI entries
- 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_AOI) = "AOI Entry", 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("U7").Resize(UBound(resBT, 1), 1) = resBT
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement