Excel VBA：在循环中选择不同的范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24435445/
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
Excel VBA: Selecting Different Ranges in Loop
提问by D. W.
I'm trying to select two different ranges in a loop (i.e. first iteration selects one range, second iteration selects another range). Here's my try at just seeing if I could do a simple task like selecting two ranges one after another:
我试图在循环中选择两个不同的范围（即第一次迭代选择一个范围，第二次迭代选择另一个范围）。这是我的尝试，看看我是否可以做一个简单的任务，比如一个接一个地选择两个范围：
Sub SelectingTwoRanges()
Dim i As Integer
Dim m As Integer
Dim n As Integer
For i = 1 To 2
m = i * 50 - 48
n = i * 50 + 1
Range(Cells(m, 1), Cells(n, 2)).Select
Next i
End Sub
This gives the error: "Method 'Cells' of Object '_Global' Failed"
这给出了错误：“对象'_Global'的方法'Cells'失败”
I've tried it before as:
我以前试过它：
Sub SelectingTwoRanges()
Dim i As Integer
Dim m As Integer
Dim n As Integer
For i = 1 To 2
m = i * 50 - 48
n = i * 50 + 1
Range("Am:Bn").Select
Next i
End Sub
Or:
或者：
Sub SelectingTwoRanges()
Dim i As Integer
Dim m As Integer
Dim n As Integer
For i = 1 To 2
m = i * 50 - 48
n = i * 50 + 1
Range("A$n:B:m").Select
Next i
End Sub
And none of the above worked. I think my problem is using loop-dependent variable inside strings (which I tried to avoid by using the "Cells(#, #)..." formation inside the "Range()". But now I'm just stuck.
以上都没有奏效。我认为我的问题是在字符串中使用依赖于循环的变量（我试图通过在“Range()”中使用“Cells(#, #)...”来避免这种情况。但现在我被卡住了。
Is this possible?
这可能吗？
回答by Alter
It's likely you're getting the "Method 'Cells' of Object '_Global' Failed" error because your's Cells() method isn't attached to an object. According to this post (Method 'Range' of object '_Global' failed. error), you can avoid that by using "activeSheet".
由于您的 Cells() 方法未附加到对象，因此您可能会收到“Method 'Cells' of Object '_Global' Failed”错误。根据这篇文章（Method 'Range' of object '_Global' failed. error），您可以通过使用“activeSheet”来避免这种情况。
Sub SelectingTwoRanges2()
Dim i As Integer
Dim m As Integer
Dim n As Integer
For i = 1 To 2
m = i * 50 - 48
n = i * 50 + 1
With ActiveSheet
.Range(.Cells(m, 1), .Cells(n, 2)).Select
End With
Next i
End Sub
As for your other examples; you're completely right. Including a variable name in a string doesn't reference the variable at all. You can use the following format to concatenate strings (VBA will automatically try to convert variable values to strings in concatenation)
至于你的其他例子；你是完全正确的。在字符串中包含变量名称根本不引用该变量。您可以使用以下格式连接字符串（VBA 会自动尝试在连接中将变量值转换为字符串）
Range("A" & m & ":B" & n).Select
Personally I like using CStr() to make sure VBA converts a value to a string
我个人喜欢使用 CStr() 来确保 VBA 将值转换为字符串
Range("A" & CStr(m) & ":B" & CStr(n)).Select
回答by asp8811
The first example worked for me, but try:
第一个例子对我有用，但请尝试：
activesheet.Range(activesheet.Cells(m, 1), activesheet.Cells(n, 2)).Select
This will fully qualify the Cells() range reference.
这将完全限定 Cells() 范围引用。
