如何修复由我的单元格范围VBA触发'Application-defined or object-defined error'

Question

每次我尝试运行宏时，LIRCounter包围的第一个部分运行得很好，但Excel返回"运行时错误‘1004’：应用程序定义或对象定义的错误"，调试会突出显示我的Range(…)。合并宏其余部分的部分。

我尝试通过删除.Merge、Counter变量并重写整个代码来重新格式化该范围，但我无法缩小导致错误的范围。

我尝试通过删除.Merge、Counter变量并重写整个代码来重新格式化该范围，但我无法缩小导致错误的范围。

Sub MergeCells()
Set Worksheet = Worksheets("Technical Data")
With Worksheet
For LIRCounter = 44 To 15 Step -1
If .Cells(LIRCounter, 19).Value = Not IsEmpty(Cells(LIRCounter, 19)) Then
Else
.Range(.Cells(LIRCounter, 21), .Cells(LIRCounter, 26)).Merge
End If
If .Cells(LIRCounter, 19).Value = Not IsEmpty(Cells(LIRCounter, 19)) Then
Else
.Range(.Cells(LIRCounter, 21), .Cells(LIRCounter, 26)) = "N/A"
End If
Next LIRCounter
For ETCounter = 44 To 15 Step -1
If .Cells(ETCounter, 3).Value = "Structural" Then
.Range(.Cells(ETCounter, 4), .Cells(ETCounter, 12)).Merge
End If
If .Cells(ETCounter, 3).Value = "Structural" Then
.Range(.Cells(ETCounter, 4), .Cells(ETCounter, 12)) = "N/A - Structural"
End If
Next ETCounter
For ETCounter2 = 44 To 15 Step -1
If .Cells(ETCounter2, 3).Value = "Structural" Then
.Range(.Cells(ETCounter2, 15), .Cells(ETCounter2, 26)).Merge
End If
If .Cells(ETCounter2, 3).Value = "Structural" Then
.Range(.Cells(ETCounter2, 15), .Cells(ETCounter2, 26)) = "N/A - Structural"
End If
Next ETCounter2
End With
End Sub

预期结果是，如果从单元格C15的下拉菜单中选择"Structural"，则单元格D15:L15合并为一个单元格，单元格O15:Z15合并为另一个单元格。合并后的两个单元格都显示"N/A-Structural"。从第44行开始的每一行也是如此。当宏运行时，它只返回"运行时错误‘1004’：应用程序定义或对象定义的错误"，并且不会合并任何单元格。

应该如何工作

Answer 1

建议您可以更简单地编写代码，删除大量冗余代码。只有一个for loop，因为所有三个循环都相同，只有两个if else。为什么会出现错误？它对我来说是不可复制的。试着注释掉大部分代码，并试图缩小错误的来源。错误的来源也可能是工作表中的数据，因此请先尝试在空/伪工作表中运行代码。HTH。

Option Explicit
Sub MergeCells()
Dim TechnicalDataSheet As Worksheet
Dim counter As Long
Set TechnicalDataSheet = Worksheets("Technical Data")
With TechnicalDataSheet 
For counter = 44 To 15 Step -1
If .Cells(counter, 19).Value = "" Then
.Range(.Cells(counter, 21), .Cells(counter, 26)).Merge
.Range(.Cells(counter, 21), .Cells(counter, 26)) = "N/A"
End If
If .Cells(counter, 3).Value = "Structural" Then
.Range(.Cells(counter, 4), .Cells(counter, 12)).Merge
.Range(.Cells(counter, 4), .Cells(counter, 12)) = "N/A - Structural"
.Range(.Cells(counter, 15), .Cells(counter, 26)).Merge
.Range(.Cells(counter, 15), .Cells(counter, 26)) = "N/A - Structural"
End If
Next counter
End With
End Sub