Concatenating Variables Into String to be Set to a Range in VBA

variables excel range concatenation vba

63,710

Solution 1

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

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.

Solution 2

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:

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.

63,710

H3lue

Updated on August 07, 2020

Comments

H3lue almost 4 years
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.

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 = "A" & rowCount & ":" & Mid(alphabet, totHdrLngth, 1) & belowRowCount
curRange = ActiveSheet.Range(inRange)
```
But that did not help (as I thought it wouldn't). Any suggestions?
- Jacob almost 13 years
  
  Could you use Debug.Print inRange to get the value of inRange into the debug window (ctrl+g)?
- H3lue almost 13 years
  
  Hmm.. not seeing anything in the debug window. Running my cursor over "inRange" in debug mode and says the string is empty (inRange = "")
- Jacob almost 13 years
  
  I just want to know how the range string looks when you call range, maybe you could MsgBox it or something.
- H3lue almost 13 years
  
  inRange prints as empty (nothing shows up)
- Jacob almost 13 years
  
  inRange = "A" & rowCount & ":" & Mid(alphabet, totHdrLngth, 1) & belowRowCount equals nothing?!
- Paul McLain almost 13 years
  
  What are the values of rowCount, totHdrLngth, and belowRowCount for the instance when you get the error?
- Tiago Cardoso almost 13 years
  
  I'd bet the problem is within the Mid(alphabet, totHdrLngth, 1) code... as @cularis suggested, try to print the result of this specific instruction to see if it is REALLY returning only a uppercase letter.
- H3lue almost 13 years
  
  totHdrLngth's value is zero, thats the problem... but it shouldn't be zero since i defined it equal to 10 in another sub. It is a Global.
- Paul McLain almost 13 years
  
  Post the code of that sub and anywhere else you're editing the variable. Did you step through all of your code?
- H3lue almost 13 years
  
  I actually fixed that particular error, but the appropriate range is still not being selected. I am given the error now on the line below: 'code' Run-time error '91': Object vairable or With block variable not set
- Gary McGill almost 13 years
  
  When you assign a value to a variable of type <any object>, you need to use the Set keyword. So that curRange assignment looks like it needs one. Without it, you'll get the error "Object variable or With Block" error you describe.
- Lance Roberts almost 13 years
  
  @H3lue, now that you've fixed the totHdrLngth problem, just go back to the first statement you had.
Lance Roberts almost 13 years

Excel VBA will cast them to the needed string.
dreojs16 over 4 years

If you use the string concatenator ampersand(&) any integer will be auto-cast to a string