2

Something has changed in my VBA that is not allowing me to complete certain routines. I have listed a very simple example below. If anyone has experienced this I would be really appreciate any support in resolving this issue.

  • Simple example of issue:

When I use the following code it works fine.

Sheets("Sheet1").Select
Range("B3").Select

When I combine them I get a "1004" error

Sheets("Sheet1").Range("B3").Select

I checked the reference/document library and nothing appears to have changed in here. It has to be something simple but I just can't put my finger on it.

Community
  • 1
  • 1
user1624926
  • 441
  • 2
  • 6
  • 16
  • This seriously should work... what OS and version of Excel are you on? – Hambone Sep 30 '16 at 13:42
  • Also, is your code in a module, associated with a worksheet or somewhere else? I suppose if this is within a worksheet, I can see why this *might* fail – Hambone Sep 30 '16 at 13:45

2 Answers2

3

You already have your answer:

  • first Select the worksheet
  • the Select a range on that worksheet

Your code will work if you happen to be on Sheet1 when it is run, but will fail if you are not on Sheet1. In my opinion VBA is kind of dumb with regard to this issue.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    more precisely you cannot select a range if sheet isn't activated. Sounds fair IMHO – Pierre Sep 30 '16 at 13:49
  • @Pierre Your explanation is better than mine. – Gary's Student Sep 30 '16 at 13:50
  • Thank you guys - really appreciate your input. I was totally unaware of this and I can't believe I never discovered this up to now. I never use ".select" and it's probably part of the reason. Thanks for your support guys. – user1624926 Sep 30 '16 at 14:00
3

If you absolutely must do it in a single line of code then swap the Select for an Application.GoTo which accepts both worksheet and cell range.

application.goto range("Sheet1!B3")

However, it is almost never necessary (and most often counter-productive) to use the Range .Select method to reference a cell or cells to work on. See How to avoid using Select in Excel VBA macros for methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1
  • Thank you for your guidance and appreciate your support. The link is very handy. I can't believe I never came across this issue up to now. Thanks again. – user1624926 Sep 30 '16 at 14:01