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

Question

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?

Could you use Debug.Print inRange to get the value of inRange into the debug window (ctrl+g)? — Jacob
– Jacob, Commented Aug 4, 2011 at 17:36
Hmm.. not seeing anything in the debug window. Running my cursor over "inRange" in debug mode and says the string is empty (inRange = "") — H3lue
– H3lue, Commented Aug 4, 2011 at 17:51
I just want to know how the range string looks when you call range, maybe you could MsgBox it or something. — Jacob
– Jacob, Commented Aug 4, 2011 at 17:53
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. — Gary McGill
– Gary McGill, Commented Aug 4, 2011 at 22:28

Gaijinhunter · Accepted Answer · 2011-08-07 07:07:23Z

5

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.

answered Aug 7, 2011 at 7:07

Gaijinhunter

14.7k4 gold badges53 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

soytsauce · Accepted Answer · 2011-08-04 22:41:57Z

1

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.

answered Aug 4, 2011 at 22:41

soytsauce

3812 gold badges4 silver badges17 bronze badges

2 Comments

Lance Roberts Over a year ago

Excel VBA will cast them to the needed string.

dreojs16 Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related