Select variable Range (by Cells function)

[Legacy 146544]

Hi all,

I need to select a range (on a different worksheet than the active one) based on input so I created this code:

ThisWorkbook.Worksheets("Debiteuren").Activate
Worksheets("Debiteuren").Range(Cells(RLocatie - 1, 1), Cells(RLocatie - 1, 52)).Select

Where RLocatie is the row number.

But I get runtime error 1004 on the second line of code. For some reason Excel can't select the range?

Does anyone know what might be the problem?

 

[Richard Schollar]

Hi

You can't select a range on a worksheet that isn't active.

Your code shouldn't fail, however, as you have activated the relevant worksheet first - is the code in a sheet module rather than in a standard module?

 

[Legacy 146544]

And:

if I run the macro from the worksheet where the button (which triggers the macro) is located, the code works fine!

The button is on Sheet "wIntern"

This code:
Worksheets("Debiteuren").Range(Cells(RLocatie - 1, 1), Cells(RLocatie - 1, 52)).Select
Works perfectly!

What might be the case?

 

[Legacy 146544]

That might be it... the code is in a sheet module.

 

[Richard Schollar]

When you have code in a sheet module, any not-fully-referenced calls to objects refer to the sheet in which the code appears so in your line:

Worksheets("Debiteuren").Range(Cells(RLocatie - 1, 1), Cells(RLocatie - 1, 52)).Select

You have a Range referenced to sheet "Debiteuren" but Cells referenced to sheet "wIntern" hence a mismatch. You can avoid this by fully referening:

With ThisWorkbook.Worksheets("Debiteuren")
  .Activate
  .Range(.Cells(RLocatie - 1, 1), .Cells(RLocatie - 1, 52)).Select
End With

Note the periods before the Cells property. (usual caveats of "you don't normally need to Select anything apply)

 

[Legacy 146544]

Yes that is the problem!

And your code is the solution I was looking for.

I can go writing code now, Thank you Richard

 

[Legacy 146544]

Richard,

is there a way to overcome the problem by code like: worksheets(sheetx).cells(2,1)...?

Instead of the with solution

That would be very handy for future excel work for me.

Thx in advance.

 

[Richard Schollar]

Yep - it's more verbose and won't execute quite as quickly (not that you're ever likely to notice):

Worksheets("Debiteuren").Activate
Worksheets("Debiteuren").Range(Worksheets("Debiteuren").Cells(RLocatie - 1, 1), Worksheets("Debiteuren").Cells(RLocatie - 1, 52)).Select

Or you could use Resize:

Worksheets("Debiteuren").Cells(RLocatie - 1,1).Resize(,52).Select