Find and replace a word from excel. application-defined or object-defined error

Question

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

Getting Run-time error '1004':

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

Please help

Original code http://stackoverflow.com/questions/23408568/find-and-replace-multiple-words-in-word-from-excel-list — user3766722, Sep 10 '14 at 07:19

score 2 · Answer 1 · answered Sep 10 '14 at 07:40

2

I suspect you don't have a reference set to the Excel object library in which case xlDown has no value. Either use:

Set rng = ws.Range("A1", ws.Range("A1").End(-4142))

or, preferably, define the constant yourself at the top of your module:

Const xlDown as Long = -4142

answered Sep 10 '14 at 07:40

Rory

32,730
5
32
35

+ 1 I suspect that is the case too :) However why xlDown and not xlup to create the range? – Siddharth Rout Sep 10 '14 at 07:45
@SiddharthRout I don't know the layout of the workbook so just left it as it was. :) – Rory Sep 10 '14 at 07:47

score 1 · Accepted Answer · edited Sep 11 '14 at 12:39

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.

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

And declare this at the top

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

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)

And declare this at the top

Const xlUp As Long = -4162

Find and replace a word from excel. application-defined or object-defined error

2 Answers2

Linked