vba 基于变量excel vba选择整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10587004/
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
selecting an entire row based on a variable excel vba
提问by thebiglebowski11
I am trying to select an entire row in a different sheet and then copy the row to the sheet I am currently in with a macro. The code works fine if the Rows() sub is passed integers hardcoded but when I put a variable I get the "Select method of Range class failed" error. here is the code I have:
我试图在不同的工作表中选择整行，然后将该行复制到我当前使用宏所在的工作表中。如果 Rows() 子是硬编码的整数，则代码工作正常，但是当我放置一个变量时，我收到“范围类的选择方法失败”错误。这是我的代码：
Sheets("BOM").Select
Rows(copyFromRow & ":" & copyFromRow).Select
Selection.Copy
Sheets("Proposal").Select
Rows(copyToRow & ":" & copyToRow).Select
ActiveSheet.Paste
copyToRow = copyToRow + 1
Rows(copyToRow & ":" & copyToRow).Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
if instead i used :
如果相反，我使用了：
Rows("52:52").Select
Selection.Copy
it works fine, but when the variable is there, the error occurs.
它工作正常，但是当变量存在时，就会发生错误。
Thanks
谢谢
回答by assylias
I just tested the code at the bottom and it prints 16384twice (I'm on Excel 2010) and the first row gets selected. Your problem seems to be somewhere else.
我刚刚测试了底部的代码，它打印了16384两次（我在 Excel 2010 上）并且第一行被选中。您的问题似乎在其他地方。
Have you tried to get rid of the selects:
你有没有试图摆脱选择：
Sheets("BOM").Rows(copyFromRow).Copy
With Sheets("Proposal")
.Paste Destination:=.Rows(copyToRow)
copyToRow = copyToRow + 1
Application.CutCopyMode = False
.Rows(copyToRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End With
Test code to get convinced that the problem does not seem to be what you think it is.
测试代码以确信问题似乎不是您认为的那样。
Sub test()
Dim r
Dim i As Long
i = 1
r = Rows(i & ":" & i)
Debug.Print UBound(r, 2)
r = Rows(i)
Debug.Print UBound(r, 2)
Rows(i).Select
End Sub
回答by John Noel
Saw this answer on another site and it works for me as well!
在另一个网站上看到这个答案，它也适用于我！
Posted by Shawn on October 14, 2001 1:24 PM
var1 = 1
var2 = 5
Rows(var1 & ":" & var2).SelectThat worked for me, looks like you just have to keep the variables outside the quotes and add the and statement (&)
-Shawn
Shawn 于 2001 年 10 月 14 日下午 1:24 发表
var1 = 1
var2 = 5
Rows(var1 & ":" & var2).Select这对我有用，看起来您只需要将变量保留在引号之外并添加 and 语句 (&)
-肖恩
回答by Felix
I solved the problem for me by addressing also the worksheet first:
我通过首先解决工作表为我解决了这个问题：
ws.rows(x & ":" & y).Select
without the reference to the worksheet (ws) I got an error.
没有参考工作表（ws）我得到了一个错误。
回答by kirbs
You need to add quotes. VBA is translating
您需要添加引号。VBA 正在翻译
Rows(copyToRow & ":" & copyToRow).Select`
into
进入
Rows(52:52).Select
Try changing
尝试改变
Rows(""" & copyToRow & ":" & copyToRow & """).Select
回答by Travis
The key is in the quotes around the colon and &, i.e. rows(variable & ":" & variable).select
关键是在冒号和 & 周围的引号中，即行（变量 & ":" & 变量）。选择
Adapt this:
调整这个：
Rows(x & ":" & y).select
where x and y are your variables.
其中 x 和 y 是您的变量。
Some other examples that may help you understand
其他一些可以帮助您理解的示例
Rows(x & ":" & x).select
Or
或者
Rows((x+1) & ":" (x*3)).select
Or
或者
Rows((x+2) & ":" & (y-3)).select
Hopefully you get the idea.
希望你能明白。
