0

I have 3 worksheets and already declared them but my code 'range(cells)' for sheets 2 and 3 isn't working when I'm on sheet 1. it shows this:

Run-time error '1004'
Method 'Range' of object '_Worksheet'failed

and this is my code:

Dim d As Worksheet, m As Worksheet
Dim x As Integer, y As Integer, z As Integer

m.Range(Cells(x + 1, 2), Cells(y - z + 4, 2)) = "apple"
m.Range(Cells(x + 2, 2), Cells(y - z + 5, 2)) = "banana"
m.Range(Cells(x + 3, 2), Cells(y - z + 6, 2)) = "carrot"
m.Range(Cells(x + 4, 2), Cells(y - z + 7, 2)) = "donut"
m.Range(Cells(x + 6, 2), Cells(y - z + 9, 2)) = "egg"
m.Range(Cells(x + 7, 2), Cells(y - z + 10, 2)) = "fruit"
Azalea
  • 1
  • 2
  • 1
    You need to use `m.Cells` not just `Cells` in each case. – Rory May 27 '22 at 09:13
  • Since you're using `m` for `Range` you need to do the same for `Cells`: `m.Cells`. It's hard to help with so little detail and without seeing the rest of the code. You can [edit your post](https://stackoverflow.com/posts/72402804/edit) at any time – VBasic2008 May 28 '22 at 00:14

1 Answers1

0

The proper notation:

Set d = Thisworkbook.Sheets(1)
Set m = Thisworkbook.Sheets(2)

Range(m.Cells(x + 1, 2), m.Cells(y - z + 4, 2)) = "apple"
AcsErno
  • 1,597
  • 1
  • 7
  • 10