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
– Stupid_Intern, Commented Mar 18, 2017 at 13:50
You state '(I20 is constant though)'. Do you mean I2 or J20 ? — user4039065
– user4039065, Commented Mar 18, 2017 at 13:50

Shai Rado · Accepted Answer · 2017-03-18 13:59:14Z

1

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

answered Mar 18, 2017 at 13:59

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:17:17Z

0

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?

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Mar 18, 2017 at 13:53

user4039065

Comments

user3598756 · Accepted Answer · 2017-03-18 15:52:16Z

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, 2017 at 15:52

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related