EXCEL VBA Application defined or object-defined error '1004'

Question

I used to have a line

 Set rRng1 = Worksheets("Sheet1").Range("I2:J20")

But as the range of cells can change from file to file (I2 is constant though), I found out an easy way to automate it.

The first cell from the range is always I2, and the last cell is J(last_pair_cell)

I thought that using Range(Cells(2,9), Cells(last_pair_cell), 10) would to the trick, but I'm getting error 1004...

This is the entire code:

Sub LoopRange2()

    Dim rCell1 As Range
    Dim rCell2 As Range
    Dim rRng1 As Range
    Dim rRng2 As Range

    Dim nCol As Integer                                     'Finds week column to insert values
    nCol = Worksheets("Clube").Range("P69").Value + 5


    'Find number of pairs that played the tournment
    Dim last_pair_cell As Integer
    Dim rngX As Range

    Set rngX = Worksheets("Sheet_CSV").Range("A1:A10000").Find("Board", lookat:=xlPart)
    If Not rngX Is Nothing Then
        last_pair_cell = rngX.Row - 1
    End If



    **Set rRng1 = Worksheets("Sheet_CSV").Range(Cells(2, 9), Cells(last_pair_cell, 10))**
    'Set rRng1 = Worksheets("Sheet1").Range("I2:J20")
    Set rRng2 = Worksheets("Clube").Range("C3:C80")         'IF ERROR CHANGE C80 TO C69

    For Each rCell1 In rRng1.Cells
        For Each rCell2 In rRng2.Cells
            If rCell2.Value = rCell1.Value Then
                Worksheets("Clube").Cells(rCell2.Row, nCol).Value = Worksheets("Sheet1").Cells(rCell1.Row, 6).Value
            End If
        Next rCell2
    Next rCell1

End Sub

Use `Debug.print last_pair_cell` before the errored line to check the value — Stupid_Intern, Mar 18 '17 at 13:50
You state '*(I20 is constant though)*'. Do you mean `I2` or `J20` ? — , Mar 18 '17 at 13:50

score 1 · Answer 1 · answered Mar 18 '17 at 13:59

You are trying to use the Find method to set rngX, here :

Set rngX = Worksheets("Sheet_CSV").Range("A1:A10000").Find("Board", lookat:=xlPart)

However, if your Find was not able to find "Board", then rngX is Nothing, and you are not passing the following If criteria:

If Not rngX Is Nothing Then
    last_pair_cell = rngX.Row - 1
End If

and last_pair_cell doesn't get the value of rngX.Row - 1, instead it still has the default value of 0.

So setting your range with :

Set rRng1 = Worksheets("Sheet_CSV").Range(Cells(2, 9), Cells(last_pair_cell, 10))

Will throw an error, since Cells(last_pair_cell, 10) is actually Cells(0, 10) which throws an error.

Also, just to make sure your rRng1 is fully qualified, use the correct syntax:

With Worksheets("Sheet_CSV")
    Set rRng1 = .Range(.Cells(2, 9), .Cells(last_pair_cell, 10))
End With

score 0 · Answer 2 · edited May 23 '17 at 12:17

There is no parent worksheet associated with the Cells within Range.

Set rRng1 = Worksheets("Sheet_CSV").Range(Worksheets("Sheet_CSV").Cells(2, 9), Worksheets("Sheet_CSV").Cells(last_pair_cell, 10))

'or more succinctly as,

with Worksheets("Sheet_CSV")
    Set rRng1 =.Range(.Cells(2, 9), .Cells(last_pair_cell, 10))
end with

See Is the . in .Range necessary when defined by .Cells?

score 0 · Answer 3 · answered Mar 18 '17 at 15:52

0

to avoid running after parent worksheet qualification after the first one you could use Resize() method:

Set rRng1 = Worksheets("Sheet_CSV").Cells(2, 9).Resize(last_pair_cell - 1, 2)

answered Mar 18 '17 at 15:52

user3598756

28,893
4
18
28

EXCEL VBA Application defined or object-defined error '1004'

3 Answers3