将变量连接到要设置为VBA范围的字符串 [英] Concatenating Variables Into String to be Set to a Range in VBA
问题描述
我遇到一个特定代码行的问题:
I am having a problem with a particular line of code:
ActiveSheet.Range("A" & rowCount & ":" & Mid(alphabet, totHdrLngth, 1) & belowRowCount)
一个包含大写字母A到Z的字符串。
Where alphabet is a string containing uppercase letters A to Z.
我收到以下错误:
Run-time error '5':
Invalid Procedure call or argument
我尝试创建一个字符串inRange并将代码更改为:
I tried creating a String "inRange" and changing the code to this:
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?
推荐答案
虽然这样创建范围一般都是皱眉的,但是这样做的方式是使用SET像@Gary McGill在评论中指出)。以下是一个如何做的例子:
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:
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
您正在当前范围内运行此宏,因此不需要指定ActiveSheet.Range。我希望这有助于您达到您尝试实现的目标。
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.
这篇关于将变量连接到要设置为VBA范围的字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!