Find and replace a word from excel. application-de

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

Answer 1

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

Answer 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