1

I'm new to VBA coding and for a project at work I've had to quickly teach myself some VBA. I'm trying to rewrite some code to avoid using the .select command as often as possible. The issue is have is when I define a range as a variable and then attempt to set it.

Dim SearchRng As Range

Set SearchRng = Sheets("Employee Data").Range("B9", Range("B9").End(xlDown).End(xlToRight))

The active sheet when this bit of the code is run is not the "Employee Data" sheet and I always get a "Run-time error '1004': Application-defined or object-defined error".

I've found that if I select the "Employee Data" sheet first then set the range then the code works. i.e

Dim SearchRng As Range

Worksheets("Employee Data").Select
Set SearchRng = Range("B9",Range("B9").End(xlDown).End(xlToRight))

This however defeats the purpose of trying to avoid the .select command.

Can anyone explain why the first bit of code doesn't work but then second bit of code does.

Let me know what additional info might be needed to help solve this problem (I'm new to VBA so unsure what is needed).

Community
  • 1
  • 1
Terry Hemans
  • 13
  • 3
  • what is the range you are trying to define ? from "B9" until where ? what cell is `Range("B9").End(xlDown).End(xlToRight)` suppose to be ? The code in my answer below will run without errors (not sure about what range you wanted though) – Shai Rado Nov 29 '16 at 09:09
  • Thanks. The range I'm referring to is essentially a database stored on the "Employee Data" sheet. B9 is the top left cell and I want the entire database selected, hence the .End commands. The intention is to define the range so that it can be searched. – Terry Hemans Nov 29 '16 at 12:37
  • did you test my code below ? did you get the result you needed ? – Shai Rado Nov 29 '16 at 12:39
  • Yes, I did thanks many much. – Terry Hemans Nov 29 '16 at 12:49

1 Answers1

2

Not sure I know what Range you are trying to define with this line: Range("B9", Range("B9").End(xlDown).End(xlToRight)).

The code below will run without errors with the Range you wanted to define, and without the need to Select "Employee Data" sheet first.

Option Explicit

Sub DefineRange()

Dim SearchRng As Range

With Sheets("Employee Data")
    Set SearchRng = .Range(.Range("B9"), .Range("B9").End(xlDown).End(xlToRight))
End With

' for debug purposes
Debug.Print SearchRng.Address

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51