application-defined or object-defined error when setting range variable

Question

I have a Range variable and a separate workbook which I try to fill with values. I use TheRange for it, first setting it to the range of clientsColl.Count rows and 1000 columns.

Dim TheRange As Range

With resultWorkbook.Worksheets("matrix_random")

    Set TheRange = _
        .Range(Cells(2, 2), Cells(clientsColl.Count, _
                                     1000)))

...
End With

However, I keep getting this error, can't figure out why..

Application-defined or object-defined error

you need the little . for the Cells, try .Range(.Cells(2, 2), .Cells(1000, clientsColl.Count))) , and you have the Rows and Columns switched in the second Cells — Shai Rado
– Shai Rado, Commented Nov 21, 2017 at 13:28

Shai Rado · Accepted Answer · 2017-11-21 13:33:13Z

3

Just for future users to get the an answer.

You need to qualify your .Cells with the With resultWorkbook.Worksheets("matrix_random") statement.

Also, the second Cells has Rows and Columns swapped.

Code

Dim TheRange As Range

With resultWorkbook.Worksheets("matrix_random")
    Set TheRange = .Range(.Cells(2, 2), .Cells(1000, clientsColl.Count)) '<-- you have 1 "extra" closing bracket
End With

answered Nov 21, 2017 at 13:33

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

1 Comment

Subodh Tiwari sktneer Over a year ago

Good catch. plus 1

Collectives™ on Stack Overflow

application-defined or object-defined error when setting range variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related