假设我想使用counta(rangefrom:rangeto(函数来查看我的单元范围是否有任何数据。是否可以将范围和rangeto变成变量?我想下一张纸检查一个20x20盒子,然后循环到下面的下一个20x20框中,其中包装盒之间装有文字。但是,我不知道如何将变量放入counta函数中,或者是否可以将变量放在可能的情况下,以便我可以跳过填充包含文本的单元格的行。如果有办法做到这一点,我可能没有正确地措辞,从而导致我找不到任何有关它的信息。任何帮助将不胜感激。
目前,我正在使用行和列循环整个代码,并且只是单独检查每个单元格,我想知道是否可以使用具有变量的Counta。
Dim RowCounter As Integer
Dim ColumnTraversing As Integer
Dim PopulatedCounter As Integer
Dim OverallCounter As Integer
RowCounter = 1
PopulatedCounter = 0
While (OverallCounter < 5)
ColumnTraversing = 1
With ThisWorkbook.Worksheets("Test")
While (ColumnTraversing <= 6)'column looper
While (RowCounter <= 40) 'Row Looper
'
If (.Cells(RowCounter, ColumnTraversing).Text <> "") Then
i = i + 1
End If
RowCounter = RowCounter + 1
Wend
ColumnTraversing = ColumnTraversing + 1
RowCounter = 1
Wend
If (i > 0) Then
PopulatedCounter = PopulatedCounter + 1
End If
End With
OverallCounter = OverallCounter + 1
i=0
RowCounter = RowCounter + 2
Wend
此循环通过一个6x40框,然后向下移动并进行另一个检查是否被填充,本质上是在多个变量上执行Counta函数的作用。
基于您上面的评论,我假设您想循环浏览40行 * 6列的框,并由包含文本的一行隔开。我们可以这样循环:
Sub Macro1()
Dim iRow As Integer, PCounter As Integer, numBoxes as Integer
iRow = 1: PCounter = 0: numBoxes = 0 'numBoxes counts the number of Boxes down the sheet
While (numBoxes <= 5) 'Change this to the number of boxes you have
With ThisWorkbook.Worksheets("Test")
PCounter = PCounter + WorksheetFunction.CountA(.Range("A" & iRow & ":F" & iRow + 39))
iRow = iRow + 41 ' 41 = 40 rows of boxes + 1 row of text between bixes
End With
numBoxes = numBoxes + 1
Wend
End Sub