CODEWITHSUNDEEP
excelvba
GBeltramini
GBeltramini

Reputation: 13

Set Range doesn't work with Cells() inside

I am writing a code in which I set those two ranges:

Set ColorSAP = SAP_Comparison.Range("C3:AG19")
Set ColorPlan = Production_Plan.Worksheets("Schedule").Range(Cells(4, 
MonthS), Cells(20, (MonthE - 1)))

MonthS and MonthE are integers.

Production_plan is a workbook.

SAP_Comparison is a workbook.

I have no idea why I get a error for the second but not for the first since they are very similar. I have even tested easier cases and this kind of usage do work.

Error: Run-Time '1004'
Application-defined or object-defined error.

How can I solve this?

Upvotes: 1

Views: 170

Answers (2)

DisplayName
DisplayName

Reputation: 13386

You could use Cells to get address out of and feed Range with:

Set ColorPlan = Production_Plan.Worksheets("Schedule").Range(Cells(4, MonthS).Address & “:” & Cells(20, (MonthE - 1)).Address)

This way you only have one “true” Range object and it’s completely qualified

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166850

Unqualified Cells refers to the ActiveSheet, which will raise an error if that's not "Schedule"

You need something like

With Production_Plan.Worksheets("Schedule")
    Set ColorPlan = .Range(.Cells(4, MonthS), .Cells(20, (MonthE - 1)))
End With

or

Dim sht As Worksheet

Set sht = Production_Plan.Worksheets("Schedule")
Set ColorPlan = sht.Range(sht.Cells(4, MonthS), sht.Cells(20, (MonthE - 1)))

What is the default scope of worksheets and cells and range?

Upvotes: 2

Related Questions