如果不先选择图纸，就无法设置范围变量 [英] Cannot set a range variable without selecting sheet first

问题描述

我是VBA编码的新手，对于一个工作中的项目，我必须快速自学一些VBA.我正在尝试重写一些代码，以避免尽可能频繁地使用.select命令.问题是当我将范围定义为变量然后尝试设置它时.

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))

运行此代码段时的活动工作表不是员工数据"工作表，并且我总是收到运行时错误'1004':应用程序定义的错误或对象定义的错误".

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))

但是，这违背了避免使用.select命令的目的.

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.

让我知道可能需要哪些其他信息来帮助解决此问题(我是VBA的新手，所以不确定需要什么).

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

推荐答案

不确定我知道您要用此行定义什么 Range : Range("B9"，Range("B9").End(xlDown).End(xlToRight)).

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

下面的代码将与您要定义的 Range 一起运行而不会出现错误，并且无需先选择"Employee Data"工作表.

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

这篇关于如果不先选择图纸，就无法设置范围变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！