Referencing a Range object set by Range.Row() generates Error 1004 "Application-defined or object-defined error"

Question

I've created a range object by setting it from another range object's Rows(). When I reference the newly created range by row and column, it generates error 1004.

I can reference the original range by row and column. I've included checks to make sure the rng object points to the same range as the dataset object. When I inspect the rng object, the Value2 shows a single row of data.

Below is the minimum code I'm using that generates the error.

Private Sub TestRangeObject()

Dim i As Long
Dim dataset As Range
Dim rng As Range

    Set dataset = sRoster.Range("B18:E37")

    For i = 1 To dataset.Rows.Count

        Set rng = dataset.Rows(i)

        Debug.Print "Rng is Range Obj: " & (TypeOf rng Is Range)
        Debug.Print "Same worksheet: " & (rng.Parent.CodeName = dataset.Parent.CodeName)
        Debug.Print "Same address: " & (dataset.Rows(i).Address = rng.Address)

        'can reference dataset object by row and column
        Debug.Print "First column (dataset): " & dataset(i, 1).Address

        'error when referencing rng object by row and column
        Debug.Print "First column (rng): " & rng(1, 1).Address

    Next i

End Sub

You have neither `Dim`ed nor `Set` ***sRoster***...................why ?? — Gary's Student, Jun 14 '19 at 15:55
I prefer `Dataset.cells(i, 1).Address` and `rng.Cells(1, 1).Address`. — BigBen, Jun 14 '19 at 15:58
Related: [Range() vs Cells() vs Range.Cells()](https://stackoverflow.com/q/51093274/11683) — GSerg, Jun 14 '19 at 20:10

BigBen · Accepted Answer · 2019-06-14T19:45:18.167

3

As additional detail: there's a difference between using Rows(somerow) and Range(somerange).

This can be validated with a simple example:

Sub Test()
    Dim rng As Range
    Set rng = Sheet1.Range("1:1")
    Debug.Print rng(1, 1).Address ' returns $A$1

    Dim rng2 As Range
    Set rng2 = Sheet1.Rows(1)
    Debug.Print rng2(1).Address ' succeeds, returns $1:$1
    Debug.Print rng2(1, 1).Address ' fails
End Sub

The solution - to use Rows(myRow).Cells - has already been proposed.

EDIT:

In an attempt to capture and summarize some of the back and forth from comments, a Row, whether it's Range.Rows(somerow) or Sheet.Rows(somerow), refers to a unit as a row, not as individual cells.

For example, Range("A1:E10").Rows would refer to 10 rows, not 50 cells. In the same way, Sheet1.Rows(1) refers to 1 row, not 16384 cells. The row is the "smallest unit of consideration," for lack of a better term. One row can't have a column index - it's just one row, not a collection of all the cells that make up that row, which each have their own column index.

So you'll need to use Cells if you specifically want to index the cells in a certain row.

edited Jun 14 '19 at 19:45

answered Jun 14 '19 at 18:30

BigBen

46,229
7
24
40

I am not using ````sheet1.rows(1)````. You are using a Row() on a sheet object, where I'm using Row() on an range object. Using Row() on a Range object returns a Range object. I am trying to understand why referencing row and column on a single-row range object set from a range.row() statement would fail where referencing a row and column on a single-row range object set from a range("address") would succeed. I have tried adding ````range.row(1).cells```` and it works, but I am now trying to understand why behind the differences in the setting of the range object when both return a range. – MD57780 Jun 14 '19 at 18:42
You're not using `Range.Row`, but `Range.Rows`. There's a difference. The result is still an entire `Row`, not a "row of cells". In other words, `Range.Rows(somerow)` refers to "the entire row", not each individual cell in that row, and you can't index an entire row by both a row and column number. This is why using `Cells` has been proposed. `Range.Rows(somerow).Cells` is different from `Range.Rows(somerow)`, even if the two refer to the same row on the sheet. – BigBen Jun 14 '19 at 18:43
Typo - yes I use Rows(). I appreciate the help and I'm trying to understand so forgive the continued questions. Returning an entire row as a _Range_ is still a _Range_ object. I do not understand how the method of setting a _Range_ object will impact using ````rng(1,1)````. I've not found this documented anywhere. – MD57780 Jun 14 '19 at 19:00
To clarify, both ````Sheet1.Range("A4:E4")```` and ````Sheet1.Range("A1:E10").Rows(4)```` return a _Range_ object. Sheet.Range() can be referenced by row and column. Sheet.Range.Row() cannot be referenced by row and column. Why? – MD57780 Jun 14 '19 at 19:06
1

Because it is *an entire row*, not a row of cells. An entire row has no column. It's considering the row of cells as a *row*, call it the smallest unit of consideration. – BigBen Jun 14 '19 at 19:07
When i ````Debug.Print Sheet1.Range("A1:E10").Rows(4).Address```` it gives me **$A$4:$E$4** instead of **$4:$4**. When I ````Debug.Print Sheet1.Range("A1:E10").Rows(4).Columns.Count```` it gives me **5** instead of **16384**. If it's returning the entire row, why would it limit the address and column count to the columns specified in the original range? – MD57780 Jun 14 '19 at 19:22
Not the entire row as in the entire row on the sheet, but all the cells, i.e. `$A$4:$E$4`, **considered as one entity**, a **row**. Put this way, `Sheet1.Range("A1:E10").Rows` refers to 10 *rows*, not 50 *cells*. The row is the "smallest unit of consideration," for lack of a better term. – BigBen Jun 14 '19 at 19:23
Now that helps! I'll need to dig a bit further to understand how a _Range_ object works internally, but your phrasing here helps to clarify why ````.Rows().Cells()```` resolves the issue. Think I've enough direction to dig further. Greatly appreciate the back-and-forth and tolerance to my attempt to understand. – MD57780 Jun 14 '19 at 19:34
Ahh you're good. I think I've seen this question in a different form before. Happy coding! I didn't mean any comments to come off as harsh in any way. It's hard to communicate in just chat comments... Apologies if the communication came across abrasive in any way. – BigBen Jun 14 '19 at 19:36

Gary's Student · Answer 2 · 2019-06-14T17:00:37.807

2

You can't use:

rng(1, 1)

if rng is a single row range:

Sub jksfhsa()
    Dim sRoster As Worksheet, dataset As Range, rng As Range
    Set sRoster = Sheets("Sheet1")
    Set dataset = sRoster.Range("B18:E37")
    Set rng = dataset.Rows(1)

    MsgBox dataset.Address
    MsgBox dataset(1, 1).Address

    MsgBox rng.Address
    MsgBox rng(1, 1).Address

    End Sub

The last MsgBox will fail.

However:

Sub jksfhsa()
    Dim sRoster As Worksheet, dataset As Range, rng As Range
    Set sRoster = Sheets("Sheet1")
    Set dataset = sRoster.Range("B18:E37")
    Set rng = dataset.Rows(1).Cells

    MsgBox dataset.Address
    MsgBox dataset(1, 1).Address

    MsgBox rng.Address
    MsgBox rng(1, 1).Address

    End Sub

will work just fine. So in your code replace:

Set rng = dataset.Rows(i)

with:

Set rng = dataset.Rows(i).Cells

edited Jun 14 '19 at 17:00

answered Jun 14 '19 at 16:54

Gary's Student

95,722
10
59
99

I can add a new range and set it to a single row and reference it by rng2(1,1). ````Dim rng2 As Range: Set rng2 = sRoster.Range(dataset.Rows(i).Address): Debug.Print "First column (rng2): " & rng2(1, 1).Address```` – MD57780 Jun 14 '19 at 17:37
Set `rng2 = sRoster.Range(somerange)` is not the same as `Set rng2 = sRoster.Rows(somerow)`. – BigBen Jun 14 '19 at 17:53
Both ````sRoster.Range()```` and ````Range.Row()```` return a Range object. What is the difference between the two? – MD57780 Jun 14 '19 at 18:08
@MD57780 see my answer/additional detail. – BigBen Jun 14 '19 at 18:32

Referencing a Range object set by Range.Row() generates Error 1004 "Application-defined or object-defined error"

2 Answers2

Linked