通过 VBA 选择 Range 类的方法失败

Select method of Range class failed via VBA

提问by guesthouse123

This is the code that I'm currently working with, and I'm getting this problem. I'm novice at Excel and I can't figure out what's wrong.

这是我目前正在使用的代码，我遇到了这个问题。我是 Excel 新手，无法弄清楚出了什么问题。

Private Sub cmdRecord_Click()
Sheets("BxWsn Simulation").Range("Result").Select //This is the line with the problem, as excel told me.
    Selection.Copy
    Sheets("Reslt Record").Select
    Sheets("Reslt Record").Range("A5000").End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("CuCon Simulator").Select
    Application.CutCopyMode = False
    Range("Improvement").Select
End Sub

The error is Select method of Range class failed via VBA, Error 1004. Any ideas?

错误是Select method of Range class failed via VBA, Error 1004。有任何想法吗？

ETA:

预计到达时间：

So I just changed the code to

所以我只是将代码更改为

Sheets("BxWsn Simulation").Select
Range("Result").Select

I believe this is what you mean by making it active?

我相信这就是你所说的让它活跃的意思吗？

However I'm still getting Method 'Range' of object '_Worksheet' failed, error 1004.

但是我仍然得到Method 'Range' of object '_Worksheet' failed, error 1004.

回答by GSerg

I believe you are having the same problemhere.
The sheet must be active before you can select a range on it.

我相信你在这里遇到了同样的问题。
工作表必须处于活动状态，您才能在其上选择范围。

Also, don't omit the sheet name qualifier:

另外，不要省略工作表名称限定符：

Sheets("BxWsn Simulation").Select
Sheets("BxWsn Simulation").Range("Result").Select

Or,

或者，

With Sheets("BxWsn Simulation")
  .Select
  .Range("Result").Select
End WIth

which is the same.

这是一样的。

回答by Dick Kusleika

The correct answer to this particular questions is "don't select". Sometimes you have to select or activate, but 99% of the time you don't. If your code looks like

这个特定问题的正确答案是“不要选择”。有时您必须选择或激活，但 99% 的情况下您不需要。如果你的代码看起来像

Select something
Do something to the selection
Select something else
Do something to the selection

You probably need to refactor and consider not selecting.

您可能需要重构并考虑不选择。

The error, Method 'Range' of object '_Worksheet' failed, error 1004, that you're getting is because the sheet with the button on it doesn't have a range named "Result". Most (maybe all) properties that return an object have a default Parent object. In this case, you're using the Range property to return a Range object. Because you don't qualify the Range property, Excel uses the default.

错误，对象'_Worksheet'的方法'范围'失败，错误1004，你得到的是因为上面有按钮的工作表没有名为“结果”的范围。大多数（可能是所有）返回对象的属性都有一个默认的 Parent 对象。在本例中，您使用 Range 属性返回 Range 对象。因为您不限定 Range 属性，所以 Excel 使用默认值。

The default Parent object can be different based on the circumstances. If your code were in a standard module, then the ActiveSheet would be the default Parent and Excel would try to resolve ActiveSheet.Range("Result"). Your code is in a sheet's class module (the sheet with the button on it). When the unqualified reference is used there, the default Parent is the sheet that's attached to that module. In this case they're the same because the sheet has to be active to click the button, but that isn't always the case.

默认的 Parent 对象可以根据情况而不同。如果您的代码在标准模块中，则 ActiveSheet 将是默认的 Parent，Excel 将尝试解析 ActiveSheet.Range("Result")。您的代码位于工作表的类模块（带有按钮的工作表）中。当在那里使用非限定引用时，默认的 Parent 是附加到该模块的工作表。在这种情况下，它们是相同的，因为工作表必须处于活动状态才能单击按钮，但情况并非总是如此。

When Excel gives the error that includes text like '_Object' (yours said '_Worksheet') it's always referring to the default Parent object - the underscore gives that away. Generally the way to fix that is to qualify the reference by being explicit about the parent. But in the case of selecting and activating when you don't need to, it's better to just refactor the code.

当 Excel 给出的错误包含像“_Object”（你说的“_Worksheet”）这样的文本时，它总是指的是默认的父对象 - 下划线给出了它。通常，解决该问题的方法是通过明确说明父对象来限定引用。但是在不需要的时候选择激活的情况下，还是重构一下代码比较好。

Here's one way to write your code without any selecting or activating.

这是一种无需选择或激活即可编写代码的方法。

Private Sub cmdRecord_Click()

    Dim shSource As Worksheet
    Dim shDest As Worksheet
    Dim rNext As Range

    'Me refers to the sheet whose class module you're in
    'Me.Parent refers to the workbook
    Set shSource = Me.Parent.Worksheets("BxWsn Simulation")
    Set shDest = Me.Parent.Worksheets("Reslt Record")

    Set rNext = shDest.Cells(shDest.Rows.Count, 1).End(xlUp).Offset(1, 0)

    shSource.Range("Result").Copy
    rNext.PasteSpecial xlPasteFormulasAndNumberFormats

    Application.CutCopyMode = False

End Sub

When I'm in a class module, like the sheet's class module that you're working in, I always try to do things in terms of that class. So I use Me.Parent instead of ActiveWorkbook. It makes the code more portable and prevents unexpected problems when things change.

当我在班级模块中时，例如您正在工作的工作表的班级模块，我总是尝试根据该班级做事。所以我使用 Me.Parent 而不是 ActiveWorkbook。它使代码更具可移植性，并在情况发生变化时防止出现意外问题。

I'm sure the code you have now runs in milliseconds, so you may not care, but avoiding selecting will definitely speed up your code and you don't have to set ScreenUpdating. That may become important as your code grows or in a different situation.

我确定您现在拥有的代码以毫秒为单位运行，因此您可能不在乎，但是避免选择肯定会加快您的代码速度，而且您不必设置 ScreenUpdating。随着代码的增长或处于不同的情况，这可能会变得很重要。

回答by yakovmeister

This worked for me.

这对我有用。

RowCounter = Sheets(3).UsedRange.Rows.Count + 1

Sheets(1).Rows(rowNum).EntireRow.Copy
Sheets(3).Activate
Sheets(3).Cells(RowCounter, 1).Select
Sheets(3).Paste
Sheets(1).Activate

回答by Gus Bustillos

This is how you get around that in an easy non-complicated way.
Instead of using sheet(x).rangeuse Activesheet.range("range").select

这就是你如何以一种简单而不复杂的方式解决这个问题。
而不是使用sheet(x).range使用Activesheet.range("range").select