"运行时错误 1004：对象"_Global"的方法'范围'仅对 xls 文件失败，而对 txt 或 xlsx 文件不失败

Question

i具有此片段的代码，该代码搜索短语，将范围设置为单元格在包含短语的单元格的右侧，一直到最后一个数据列，并格式化单元格有条件格式。此代码在TXT文件和XLSX文件中都可以正常工作，但是短语

Set rngHeaderAs

获取运行时错误1004：当我在XLS文件上运行代码时，对象'_global'的方法'范围'范围失败。查找部分中的短语绝对存在，如果我将非常相同的XLS文件保存为XLSX文件，则代码运行完美。

代码：

Sub Color_labreport_horizontal()
Cells.Replace What:="n,d.", Replacement:="n.d.", lookat:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
On Error Resume Next
'As
Set rngHeaderAs = Range("A1:ZZ200").Find("As*Arsen*", lookat:=xlPart) 'This string generates the error
Set rngAs = Range(rngHeaderAs, rngHeaderAs.End(xlToRight))
AsAddress = rngHeaderAs.Address(False, False)
Dim Ul1As As Double
Ul1As = 8
Dim Ul2As As Double
Ul2As = 20
Dim Ul3As As Double
Ul3As = 50
Dim Ul4As As Double
Ul4As = 600
Dim Ul5As As Double
Ul5As = 1000

With ActiveSheet
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & "<" & Ul1As & ")"
        .FormatConditions(1).Interior.ColorIndex = 33
        .FormatConditions(1).Borders.LineStyle = xlContinuous
        .FormatConditions(1).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul1As & ";" & AsAddress & "<" & Ul2As & ")"
        .FormatConditions(2).Interior.ColorIndex = 4
        .FormatConditions(2).Borders.LineStyle = xlContinuous
        .FormatConditions(2).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul2As & ";" & AsAddress & "<" & Ul3As & ")"
        .FormatConditions(3).Interior.ColorIndex = 6
        .FormatConditions(3).Borders.LineStyle = xlContinuous
        .FormatConditions(3).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul3As & ";" & AsAddress & "<" & Ul4As & ")"
        .FormatConditions(4).Interior.ColorIndex = 45
        .FormatConditions(4).Borders.LineStyle = xlContinuous
        .FormatConditions(4).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul4As & ";" & AsAddress & "<" & Ul5As & ")"
        .FormatConditions(5).Borders.LineStyle = xlContinuous
        .FormatConditions(5).Borders.Weight = xlThin
        .FormatConditions(5).Interior.ColorIndex = 3
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul5As & ")"
        .FormatConditions(6).Interior.ColorIndex = 7
        .FormatConditions(6).Borders.LineStyle = xlContinuous
        .FormatConditions(6).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=LEFT(" & AsAddress & ";1)=""<"""
        .FormatConditions(7).Interior.ColorIndex = 33
        .FormatConditions(7).Borders.LineStyle = xlContinuous
        .FormatConditions(7).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=(" & AsAddress & ") = ""n.d."""
        .FormatConditions(8).Interior.ColorIndex = 33
        .FormatConditions(8).Borders.LineStyle = xlContinuous
        .FormatConditions(8).Borders.Weight = xlThin
    End With
End With

知道为什么只有在我在XLS文件上运行时会发生这种情况？

Answer 1

罗恩·罗森费尔特(Ron Rosenfelt(在评论中给了我答案。ZZ200超出了允许范围，因此我使用CurrentRegion：

，而不是将范围定义为A1：ZZ200(或其他硬编码范围(。

 Set rngHeaderAs = Range("A1").CurrentRegion.Find("As*Arsen*", lookat:=xlPart)

确保我覆盖所需的范围而不超过允许范围。现在，该代码也完美地在Excel 97兼容的文件中运行。