将变量连接成字符串以在 VBA 中设置为范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6945949/
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
Concatenating Variables Into String to be Set to a Range in VBA
提问by H3lue
I am having a problem with a particular line of code:
我遇到了特定代码行的问题：
ActiveSheet.Range("A" & rowCount & ":" & Mid(alphabet, totHdrLngth, 1) & belowRowCount)
Where alphabet is a string containing uppercase letters A to Z.
其中字母表是包含大写字母 A 到 Z 的字符串。
I keep getting the following error:
我不断收到以下错误：
Run-time error '5':
Invalid Procedure call or argument
I tried creating a String "inRange" and changing the code to this:
我尝试创建一个字符串“inRange”并将代码更改为：
inRange = "A" & rowCount & ":" & Mid(alphabet, totHdrLngth, 1) & belowRowCount
curRange = ActiveSheet.Range(inRange)
But that did not help (as I thought it wouldn't). Any suggestions?
但这并没有帮助（因为我认为它不会）。有什么建议？
回答by aevanko
Although creating ranges like this is frowned upon in general, the way to do it is with the word SET (like @Gary McGill stated in the comments). Here is an example of how to do this:
虽然创建这样的范围通常是不受欢迎的，但这样做的方法是使用 SET 这个词（就像@Gary McGill 在评论中所说的那样）。以下是如何执行此操作的示例：
Sub test()
Dim alphabet As String
Dim totHrdrLngth As Long
Dim belowRowCount As Long
Dim rowCount As Long
Dim inRange As Range
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
totHrdrLngth = 5
belowRowCount = 10
rowCount = 5
' Gives us A5:E10
Set inRange = Range("A" & rowCount & ":" & range2 & _
Mid$(alphabet, totHrdrLngth, 1) & belowRowCount)
End Sub
You are running this macro in the current range, so there should be no need to specify ActiveSheet.Range. I hope this helps get you toward what you are trying to achieve.
您正在当前范围内运行此宏，因此无需指定 ActiveSheet.Range。我希望这可以帮助您实现您正在努力实现的目标。
回答by soytsauce
As far as I can tell, you're getting an error because your types don't match up. I imagine rowCount is an integer, as is belowRowCount. If you convert them to strings before concatenating them, you can fix it. str() will convert an integer to a string with a space before it, and LTrim() will remove the space. Try code as below:
据我所知，您收到错误是因为您的类型不匹配。我想 rowCount 是一个整数，就像下面的 RowCount 一样。如果在连接它们之前将它们转换为字符串，则可以修复它。str() 将一个整数转换为一个前面有空格的字符串，而 LTrim() 将删除该空格。尝试代码如下：
Dim sRowCount As String
Dim sBelowRowCount As String
and later
然后
sRowCount = LTrim(Str(RowCount))
sBelowRowCount = LTrim(Str(belowRowCount))
inRange = "A" & sRowCount & ":" & Mid(alphabet, totHdrLngth, 1) & sBelowRowCount
curRange = ActiveSheet.Range(inRange)
Hope this helps.
希望这可以帮助。
