1

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?

0m3r
  • 12,286
  • 15
  • 35
  • 71
GBeltramini
  • 13
  • 6

2 Answers2

2

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?

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • What is the difference between using ".Range" and ".Cells" and "Range" and "Cells"? – GBeltramini Nov 06 '18 at 19:34
  • I mean `Cells(x, y)` (unqualified with a worksheet reference) vs. `Production_Plan.Worksheets("Schedule").Cells(x, y)` (qualified with a specific worksheet in a specific workbook) – Tim Williams Nov 06 '18 at 19:35
  • When you use a `With` block the leading `.` on (eg) `.Cells()` "connects" that to the object specified by the `With` line – Tim Williams Nov 06 '18 at 19:36
0

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

DisplayName
  • 13,283
  • 2
  • 11
  • 19