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

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

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related