Range fails if sheet is not selected

Question

I have this code which should work fine

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Auswertung")
...
ws.Range(Cells(2, 2), Cells(lastRow, lastCol)).NumberFormat = "0"

It however fails if the sheet is not selected. Error 1004, Methode Range fails for the object.

If I change it to

ws.Select
ws.Range(Cells(2, 2), Cells(lastRow, lastCol)).NumberFormat = "0"

it works.

why? In VBA I want to avoid select.

@theVBE-it'srightforme Cause otherwise`Cells`refers implicit to ActiveSheet, what may be the wrong sheet. — ComputerVersteher, Dec 12 '19 at 06:40
Unqualified Cells / Range objects always point to the ActiveSheet. When "Auswetung" is selected, Cells points to it. If another sheet is selected, it points to that one. The reason you're getting an error is because you're trying to create a range on ws (`ws.Range`) using cells that are on another sheet (`[ActiveSheet.]Cells(2,2)`) — maciej, Dec 12 '19 at 06:41
use: `With ws .Range(.Cells(2, 2), .Cells(lastRow, lastCol)).NumberFormat = "0" End with` — alowflyingpig, Dec 12 '19 at 07:24
This has been answered more than 10 times and should be closed as a duplicate :) — Siddharth Rout, Dec 12 '19 at 08:09
https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range — Siddharth Rout, Dec 12 '19 at 08:10
https://stackoverflow.com/questions/18962890/2-ways-for-clearcontents-on-vba-excel-but-1-work-fine-why — Siddharth Rout, Dec 12 '19 at 08:15

score 5 · Answer 1 · answered Dec 12 '19 at 07:38

you need to qualify Cells too... The Cells reference does not automatically assume it's referencing the same sheet as your ws.Range like you are expecting. It's using the assumed ActiveSheet unlike your Range object.

Option 1:

ws.Range(ws.Cells(2, 2), ws.Cells(lastRow, lastCol)).NumberFormat = "0"

Option 2:

With ws
  .Range(.Cells(2, 2), .Cells(lastRow, lastCol)).NumberFormat = "0"
End With

Range fails if sheet is not selected

1 Answers1