查找并替换excel中的单词。应用程序定义或对象定义的错误 [英] Find and replace a word from excel. application-defined or object-defined error

问题描述

Set rng = ws.Range("A1", ws.Range("A1").End(xlDown))
For each cl in rng

获取运行时错误'1004'：

Getting Run-time error '1004':

Application-defined or object-defined error in 
**Set rng = ws.Range("A1", ws.Range("A1").End(xlDown))**

请帮助

推荐答案

Rory已经告诉过你的代码的主要问题是什么。

Rory has already told you what the main problem with your code is.

我会推荐一个稍微不同的方法来达到你想要实现的目的。

I would recommend a slightly different approach to what you are trying to achieve.

请注意，尽可能避免 xlDown 。考虑在单元格 A1 中仅存在数据的情况。在这种情况下， xlDown 将选择整个Col A.替代方法是在Col A中找到最后一行，该列具有数据，然后创建范围。

Please note that xlDown should be avoided as much as possible. Consider the scenario where there is data only in cell A1. In such a case xlDown will select the entire Col A. The alternative is to find last row in Col A which has data and then create your range.

With ws
    '~~> Find Last Row in Col A and then create the range
    '~~> oXL is the Excel Application
    If oXL.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastrow = .Cells.Find(What:="*", _
                      After:=.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastrow = 1
    End If

    Set Rng = ws.Range("A1:A" & lastrow)
End With

并在顶端

Const xlPart As Long = 2
Const xlFormulas As Long = -4123
Const xlByRows As Long = 1
Const xlPrevious As Long = 2

如果您确定Col A中总是有数据，那么您也可以尝试此操作

And if you are sure that there will always be data in Col A then you can try this as well

    lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set Rng = ws.Range("A1:A" & lastrow)

在顶端

Const xlUp As Long = -4162

这篇关于查找并替换excel中的单词。应用程序定义或对象定义的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！