使用行范围使数组变暗，并使用数组遍历For循环 [英] using row range to dim array, and use array to iterate through For loop
问题描述
我需要使用Cells（，3）的值来定义一个范围，以填充Trialnumber（18）数组。我需要数组遍历For循环，以对每个试验的列H 中的已填充单元格计数，并将计数打印到每个试验的最后一行中的T列 中。将来，我还将需要该数组用于进一步的数据分析（除非有人可以提出更好的解决方案）。
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).
目前，我正在尝试3个代码模块，试图获得所需的解决方案。
At the moment I am experimenting with 3 modules of code, trying to get the desired solution.
模块2是唯一一个没有错误的模块，并在正确的单元格中打印值，但它在打印整个已填充的单元格计数（562），而不是每次试验（预期值= 1或2）。
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).
模块1如下：
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
它在行上返回对象_global失败的方法_range错误： trialCount = Application.WorksheetFunction.CountA（Range（ H& CStr（startpoint）& ::& H& CStr（nMinusOne）））
It returns a "method _range of object _global falied" error on line: trialCount = Application.WorksheetFunction.CountA(Range("H" & CStr(startpoint) & ":" & "H" & CStr(nMinusOne)))
模块3如下：
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
它在行上返回运行时类型不匹配错误： pressedCount = A pplication.WorksheetFunction.CountA _
（.Range（.Cells（t（）， H），.Cells（.Rows.Count， H））））
It returns a run-time "type mismatch" error on line: pressedCount = Application.WorksheetFunction.CountA _
(.Range(.Cells(t(), "H"), .Cells(.Rows.Count, "H")))
编辑：我已经更新了mod 3中的代码并更新了错误。
I have updated code in mod 3 and updated error.
推荐答案
计数时，我喜欢使用字典对象，并且数组比在表上逐行处理要快。
When counting things I like to use a dictionary object, and arrays are faster than going row by row on the sheet.
这将算得上唯一Block + Trial的组合：仅按试用计数，您只需使用 k = d（r，COL_TRIAL）
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
这篇关于使用行范围使数组变暗，并使用数组遍历For循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
