I posted a similar question a week ago in Stack Overflow, but didn't get a satisfactory answer. This is the link to it: excel - how do I print a filled-cell count to adjacent column with dynamic rows? - Stack Overflow
Here is a sample of the data I want to analyse:
I have tried the suggested changes in the stack overflow thread, and at the moment the macro prints in every row, but I want it just to print in the last row of each trial, indicated by "C" or "SC" in row "P".
Here is my code:
Here is a sample of the data I want to analyse:
Excel 2010 | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
232 | Block 1 | Trial, 5 | Stimuli (5, dots10 ver1 blk1.png) | 68256 | area 14 (AOI-Exit) | AOI Exit | -37 | 109 | 1.17E+15 | 0.4771 | 0.379 | Button 7 | I | 3856 | undefined | ||||||
233 | Block 1 | Trial, 5 | Stimuli (5, dots10 ver1 blk1.png) | 68286 | area 12 | AOI Entry | -9 | 109 | 1.17E+15 | 0.4944 | 0.3797 | Button 7 | I | 3886 | undefined | ||||||
234 | Block 1 | Trial, 5 | Stimuli (5, dots10 ver1 blk1.png) | 68293 | Button 7 | 7 | Pressed | Button 7 | SC | 3893 | undefined | ||||||||||
235 | Block 1 | Trial, 2 | Stimuli (2, dots7 ver1 blk1.png) | 69416 | area 21 (AOI-Exit) | AOI Exit | -84 | 50 | 1.17E+15 | 0.4477 | 0.4447 | Button 2 | I | 3 | undefined | ||||||
full test |
I have tried the suggested changes in the stack overflow thread, and at the moment the macro prints in every row, but I want it just to print in the last row of each trial, indicated by "C" or "SC" in row "P".
Here is my code:
Code:
Sub dotcountanalysis()'define BlockNames(n) variable as "Block (n+1)" or "Transfer Block (n+1)"
Dim BlockNames(30) As String
BlockNames(0) = "Block 1"
BlockNames(1) = "Block 2"
BlockNames(2) = "Block 3"
BlockNames(3) = "Block 4"
BlockNames(4) = "Block 5"
BlockNames(5) = "Block 6"
BlockNames(6) = "Block 7"
BlockNames(7) = "Block 8"
BlockNames(8) = "Block 9"
BlockNames(9) = "Block 10"
BlockNames(10) = "Block 11"
BlockNames(11) = "Block 12"
BlockNames(12) = "Block 13"
BlockNames(13) = "Block 14"
BlockNames(14) = "Block 15"
BlockNames(15) = "Block 16"
BlockNames(16) = "Block 17"
BlockNames(17) = "Block 18"
BlockNames(18) = "Block 19"
BlockNames(19) = "Block 20"
BlockNames(20) = "Block 21"
BlockNames(21) = "Block 22"
BlockNames(22) = "Block 23"
BlockNames(23) = "Block 24"
BlockNames(24) = "Block 25"
BlockNames(25) = "Block 26"
BlockNames(26) = "Block 27"
BlockNames(27) = "Block 28"
BlockNames(28) = "Block 29"
BlockNames(29) = "Block 30"
' define TrialNames(n) variable as "Trial, (n+1)"
Dim TrialNames(18) As String
TrialNames(0) = "Trial, 1"
TrialNames(1) = "Trial, 2"
TrialNames(2) = "Trial, 3"
TrialNames(3) = "Trial, 4"
TrialNames(4) = "Trial, 5"
TrialNames(5) = "Trial, 6"
TrialNames(6) = "Trial, 7"
TrialNames(7) = "Trial, 8"
TrialNames(8) = "Trial, 9"
TrialNames(9) = "Trial, 10"
TrialNames(10) = "Trial, 11"
TrialNames(11) = "Trial, 12"
TrialNames(12) = "Trial, 13"
TrialNames(13) = "Trial, 14"
TrialNames(14) = "Trial, 15"
TrialNames(15) = "Trial, 16"
TrialNames(16) = "Trial, 17"
TrialNames(17) = "Trial, 18"
Dim i As Variant
Dim j As Variant
' begin analysis after practice trials
For Each i In BlockNames()
'count buttons pressed per trial
For Each j In TrialNames()
pressedcount = Range("H:H").Cells.SpecialCells(xlCellTypeConstants).Count
'print "pressed" count in column T in same row as "c" or "sc"
Range("T" & Range("T" & Rows.Count).End(xlUp).Row + 1).Value = pressedcount
Next j
Next i
End Sub