how to use variable ranges in different sheets

Question

I wrote a code for a product between 2 ranges. One of those ranges is in other and when i have tried to make it variable, appear some mistake. This is the code:

Sub Correlacionar()

Dim col As Integer
Dim random As Range
Dim SDesv As Range
Dim Chole As Range
col = 4

Set Chole = Sheet4.Range("a20:d23")
'Set Chole = Sheet4.Range(Cells(20, 1), Cells(19 + col, col))

Set random = Range(Cells(3, 9), Cells(3, 8 + col))
Set SDesv = Range(Cells(10, 1), Cells(10, col))
SDesv = WorksheetFunction.MMult(random, WorksheetFunction.Transpose(Chole))
Application.CutCopyMode = False
End Sub

I want to use:

Set Chole = Sheet4.Range(Cells(20, 1), Cells(19 + col, col))

Instead of:

Set Chole = Sheet4.Range("a20:d23")

What is the error you are receiving? Perhaps you can add some sample data to your question. — Matt D. Webb, Mar 05 '15 at 17:27

score 1 · Accepted Answer · answered Mar 05 '15 at 17:28

Set Chole = Sheet4.Range(Sheet4.Cells(20, 1), _
                         Sheet4.Cells(19 + col, col))

If you just use Cells then it will refer to the activesheet, not Sheet4.

It's good practice to never use either Range or Cells without a specific worksheet qualifier. Your code should not depend on any specific sheet being active.

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

Similar to Can't work with ranges if workbook and sheet are not active

Cells implicitly refers to ActiveSheet.Cells

with sheet4
   set Chole = .range(.cells(20,1), cells(19 + cells(19 + col, col))
end with

edited May 23 '17 at 11:43

Community

1
1

answered Mar 05 '15 at 17:30

Sobigen

2,038
15
23

how to use variable ranges in different sheets

2 Answers2