VBA 方法“对象范围”_Worksheet 在范围声明中使用的变量失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11307624/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA method 'range of object' _Worksheet failed variables used in range declaration
提问by user1155383
I am getting the following error message: Method range of object _worksheet failedwhen trying to select a range in excel using variables as range length.
我收到以下错误消息：Method range of object _worksheet failed尝试使用变量作为范围长度在 excel 中选择范围时。
Below is a snippet of my code:
下面是我的代码片段：
Private Function copyAmount(startRange As Integer, endRange As Integer)
Dim startRng As String
Dim endRng As String
startRng = "A" & Str(startRange)
endRng = "A" & Str(endRange)
activateBook ("book2.xlsm")
Set rng = Range(startRng, endRng)
Workbooks("book2.xlsm").Sheets(1).Range(rng).Select
Selection.Copy
activateBook ("Book1.xlsm")
Range("D3").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Function
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by Siddharth Rout
You are getting the error because you are not fully qualifying your ranges. Also it is not necessary to activate a workbook to do a copy paste :) Also you do not need a Functionfor this. Use a Sub
您收到错误是因为您没有完全限定您的范围。此外，没有必要激活工作簿来进行复制粘贴 :) 此外，您也不需要这样做Function。用一个Sub
Is this what you are trying (UNTESTED)?
难道这就是你想（未测试）？
Private Sub copyAmount(startRange As Integer, endRange As Integer)
Dim wbT As Workbook, wbO As Workbook
Dim rng As Range
Set wbT = ThisWorkbook
Set wbO = Workbooks("book2.xlsm")
Set rng = wbO.Sheets(1).Range("A" & startRange & ":" & "A" & endRange)
rng.Copy
'~~> Change Sheets(1) below to the relevant sheet
wbT.Sheets(1).Range("D3").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
EDIT
编辑
Sub, like a Functionprocedure, is a separate procedure that can take arguments, perform a series of statements, and change the value of its arguments. However a Subprocedure doesn't return a value like a Functiondoes.
Sub与Function过程一样，是一个单独的过程，可以接受参数、执行一系列语句并更改其参数的值。但是，Sub过程不会像 aFunction那样返回值。
回答by Trace
Instead of Str, use Cstr. This will make your code work I guess...
而不是Str，使用Cstr。我猜这将使您的代码工作...
回答by IndyThinker
You will get this error when your code is in a worksheet and not a module.
当您的代码位于工作表而不是模块中时，您将收到此错误。
