Excel错误处理错误(类型不匹配错误13)

Question

我正试图在VBA上执行一段代码，该代码在单元格中填充&quot-&quot；每次他在使用过的范围内遇到空白单元格时。我有两列里面有公式(公式结果是"#Value"，因为用户让它为空(，当我运行代码时，我在行上得到一个运行时错误：

If pl.Range(Split(Cells(1, coluna).Address, "$")(1) & linha).Value = ""

我尝试使用错误处理程序，但它不起作用。它只是忽略标记，并不断地给我一个运行时错误屏幕。我该怎么办？

我得到的错误：

类型不匹配(错误13(

感谢您的帮助！

Public Sub PreencheCaracterizacao()
Dim linha As Long
Dim coluna As Long
Set pl = ThisWorkbook.Worksheets("BD - Caracterização")
'Two loops: linha (line) and coluna (column)
For linha = 2 To pl.Cells.Find("*", pl.Cells(1, 1), xlFormulas, xlPart, xlByRows, lPrevious).Row
For coluna = 1 To pl.UsedRange.Columns.Count

On Error GoTo proximo
'Test if the cell is blank
If pl.Range(Split(Cells(1, coluna).Address, "$")(1) & linha).Value = "" Then

'Fill the cell with the string "-"
pl.Range(Split(Cells(1, coluna).Address, "$")(1) & linha).Value = "-"

End If 
proximo:
Next coluna
Next linha 
End Sub

Answer 1

在非空范围中替换

• 在工作表的单元格中循环时，必须考虑可能导致"类型不匹配错误"的错误值。前两个解决方案说明了一种方法
• 第三种(最准确的(解决方案使用Range.Replace method(效率高得多(和refNonEmpty的"助手"函数
• 最后一种解决方案是使用有其局限性的Worksheet.UsedRange property的一行程序

Option Explicit
Sub replaceBlanksSlow()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets("BD - Caracterizaçao")

Dim lRow As Long
lRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row

Dim lCol As Long
lCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column

Dim cValue As Variant
Dim linha As Long
Dim coluna As Long

For linha = 2 To lRow
For coluna = 1 To lCol
cValue = ws.Cells(linha, coluna).Value
If Not IsError(cValue) Then
If cValue = "" Then
ws.Cells(linha, coluna).Value = "-"
End If
End If
Next coluna
Next linha

End Sub
Sub replaceBlanksSlowConstants()
Const wsName As String = "BD - Caracterizaçao"
Const fRow As Long = 2
Const fCol As Long = 1
Const rString As String = "-"

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)

Dim lRow As Long
lRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row

Dim lCol As Long
lCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column

Dim cValue As Variant
Dim linha As Long
Dim coluna As Long

For linha = fRow To lRow
For coluna = fCol To lCol
cValue = ws.Cells(linha, coluna).Value
If Not IsError(cValue) Then
If cValue = "" Then
ws.Cells(linha, coluna).Value = rString
End If
End If
Next coluna
Next linha

End Sub
' This one uses the 'refNonEmpty' function to create a reference to the range.
Sub replaceBlanks()
Const wsName As String = "BD - Caracterizaçao"
Const First As String = "A2"
Const fCol As Long = 1
Const rString As String = "-"

Dim wb As Workbook: Set wb = ThisWorkbook

Dim fCell As Range: Set fCell = wb.Worksheets(wsName).Range(First)
Dim rg As Range: Set rg = refNonEmpty(fCell)

If Not rg Is Nothing Then
rg.Replace "", rString
End If

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to the range from a given cell (range)
'               to the last non-empty cell in its worksheet.
' Remarks:      It may fail if the worksheet is filtered.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function refNonEmpty( _
FirstCell As Range) _
As Range
If Not FirstCell Is Nothing Then
Dim rg As Range
With FirstCell.Cells(1)
Set rg = .Resize(.Worksheet.Rows.Count - .Row + 1, _
.Worksheet.Columns.Count - .Column + 1)
Dim lCell As Range
Set lCell = rg.Find("*", , xlFormulas, , xlByRows, xlPrevious)
If lCell Is Nothing Then Exit Function
Set rg = rg.Resize(lCell.Row - .Row + 1)
Set refNonEmpty = rg.Resize(, rg.Find("*", , xlFormulas, , _
xlByColumns, xlPrevious).Column - .Column + 1)
End With
End If
End Function
' Note that here you cannot control the first cell.
Sub replaceBlanksUsedRange()
ThisWorkbook.Worksheets("BD - Caracterizaçao").UsedRange.Replace "", "-"
End Sub