作为VBA的新手,我们将不胜感激。我程序的基本要点是遍历电子表格的各列,并在指定范围内计算每列中非空白单元格的数量。这是我的电子表格外观的示例。
1个 | 2 | 3 | |
---|---|---|---|
1个 | 事情 | ||
2 | 事情 | ||
3 | 事情 |
如果该列中的所有单元格均为空白,则VBA会引发1004错误,找不到任何单元格。我要说的是,如果发生1004错误,请将非空白单元的计数(nonBlank = 0)设置为零,并且如果没有错误发生,则正常计数。在类似Python的系统中,我会使用try / except。这是我的尝试。
For i = 1 To 3
On Error Resume Next
Set selec_cells = Sheet1.Range(Sheet1.Cells(FirstRow, i), Sheet1.Cells(LastRow, i)).SpecialCells(xlCellTypeVisible).Cells.SpecialCells(xlCellTypeConstants)
If Err.Number <> 1004 Then
nonBlank = 0
Else
nonBlank = selec_cells.Count
End If
On Error GoTo -1
Next i
我的问题是,当我运行此代码时,即使列2应该返回3,它每次也会吐出0。谢谢!
编辑:selec_cells是抛出错误的原因。
有没有On Error Goto -1
在VBA
,这是一个VB
事情(这些都是链接到不同的页面)。一个提示是,如果您用Google搜寻VBA
东西,只需放在VBA
要寻找的东西前面。
使用On Error Resume Next
(延迟错误捕获)时,您应该最大程度地“应用”一行或两行,并使用On Error Goto 0
(禁用错误捕获)或其他错误处理程序“关闭” 。
您对的使用On Error Resume Next
是不可接受的,因为在这种特殊情况下,我们可以测试范围:1.延迟错误处理,2.尝试设置范围,3.禁用错误处理。如果存在错误,则不会设置范围,因此If Not rg Is Nothing Then
可以将其转换为类似“如果rg是那么的东西”(双重否定)或如果已经创建了对范围的引用Then。
第二种解决方案说明了一种情况,其中主错误处理程序正在处理除SpecialCells
具有自己的错误处理程序的错误以外的所有错误。Resume Next
表示在发生错误的行之后继续行。请注意该Exit Sub
行,并注意Resume ProcExit
将代码重定向到标签的位置。
下面说明了两种处理方式。在这个阶段,我建议您使用第一个,并记得On Error Goto 0
每次使用时On Error Resume Next
(一两行)使用“结束” 。
代码
Option Explicit
Sub testOnErrorResumeNext()
Const FirstRow As Long = 2
Const LastRow As Long = 11
Dim rg As Range ' ... additionally means 'Set rg = Nothing'.
Dim nonBlank As Long ' ... additionally means 'nonBlank = 0'.
Dim j As Long
For j = 1 To 3 ' Since it's a column counter, 'j' or 'c' seems preferred.
' Since you're in a loop, you need the following line.
Set rg = Nothing
On Error Resume Next
Set rg = Sheet1.Range(Sheet1.Cells(FirstRow, j), _
Sheet1.Cells(LastRow, j)).SpecialCells(xlCellTypeVisible) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rg Is Nothing Then
nonBlank = rg.Cells.Count
Else
' Since you're in a loop, you need the following line.
nonBlank = 0
End If
Debug.Print nonBlank
Next j
End Sub
Sub testOnError()
On Error GoTo clearError
Const FirstRow As Long = 2
Const LastRow As Long = 11
Dim rg As Range ' ... additionally means 'Set rg = Nothing'.
Dim nonBlank As Long ' ... additionally means 'nonBlank = 0'.
Dim j As Long
For j = 1 To 3 ' Since it's a column counter, 'j' or 'c' seems preferred.
' Since you're in a loop, you need the following line.
Set rg = Nothing
On Error GoTo SpecialCellsHandler
Set rg = Sheet1.Range(Sheet1.Cells(FirstRow, j), _
Sheet1.Cells(LastRow, j)).SpecialCells(xlCellTypeVisible) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo clearError
If Not rg Is Nothing Then
nonBlank = rg.Cells.Count
End If
Debug.Print nonBlank
Next j
ProcExit:
Exit Sub ' Note this.
SpecialCellsHandler:
' Since you're in a loop, you need the following line.
nonBlank = 0
Resume Next
clearError:
MsgBox "Run-time error '" & Err.Number & "': " & Err.Description
Resume ProcExit
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句