CODEWITHSUNDEEP
vbaexcelruntime-error
Luis Corte-Real
Luis Corte-Real

Reputation: 65

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

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

Upvotes: 1

Views: 636

Answers (3)

user3598756
user3598756

Reputation: 29421

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)

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33662

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

Upvotes: 1

user4039065
user4039065

Reputation:

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?

Upvotes: 0

Related Questions