Using a Variable references to indicate a range of cells to merge

[Tyron]

Hey Everyone,

I was wondering if it were possible to use variables to reference cell values in a range to merge?

I am using Excel 2013.

Here is my current code:

Sub CompanyHeader()

Dim StartRange as String
Dim EndRange as String

StartRange = "A1"
EndRange = "J3"

Range("StartRange:EndRange").Merge

End Sub

I am currently getting the following error:
Runtime error 1004
Method 'Range' of object '_Global' failed

I have been searching the web and have seen most people using code such as:

Range(Cells(1,1), Cells(3,4))

but would prefer not to do if I can. Want to be able to change the StartRange and EndRange on the fly if I have to.

I have in mind to use these variables later in the function as well for cell formatting.

Thanks in advance

later

Ty

 

[Andrew Poulsom]

The syntax would be:

Range(StartRange & ":" & EndRange).Merge

 

[Tyron]

The syntax would be:

Range(StartRange & ":" & EndRange).Merge

Hey Andrew Poulsom,

Thank you for the help. It fixed my code. Once I realized that I am not supposed to put the quotations around the variable name it became clear. Although I was curious why it works since in essence once it translates the variable it is becoming "A1":"J3" and normally the format is "A1:J3". Is it just that it works both ways?

I guess it would be to easy for microsoft to provide explanations in english on their VBA objects. lol.

Thanks again.

Ty

 

[Andrew Poulsom]

The Range property expects a string expression as it's argument. If you are want to use variables you need to build the string with concatenation (&). What I posted returns "A1:J3" not "A1":"J3".

 

[Tyron]

Hey Andrew Poulsom,

Okay so let me see if I understand correctly.

When I say StartRange = "A1" that puts A1 into the variable StartRange.

So when I think about what it would look like - this is what I see in my head in succession of as it is processed:

Range(StartRange & ":" & EndRange)
Range(A1 & ":" & J3)
Range(A1:J3)

I am trying to figure out what causes the quotes to appear around the variables and concatenation.

Is it just that the Range object throws them in automatically? Maybe I am just overthinking this. Don't get me wrong I appreciate that it works. I just find it easier to learn when I can put into words how things work.

Thanks again

Ty

 

[Andrew Poulsom]

The progression is:

Range(StartRange & ":" & EndRange)
Range("A1" & ":" & "J3")
Range("A1:J3")

The Range property doesn't throw anything in automatically. Concatenation creates a string.

 

[Tyron]

Hey Andrew Poulsom,

Okay I get it now.

All three have parenthesis so that indicates three seperate stings. The concatenation joins all three items together making it one string which strips out the parenthesis and wraps one set the now single string. And this puts it in the right format for Range to use.

Thanks. Now I understand.

later

Ty

 

[Andrew Poulsom]

Technically speaking the strings are surrounded by double quotes "" not parentheses, which are brackets ().

 

[DocAElstein]

<o></o>

……. Okay

so let me see if Iunderstand correctly…When I say StartRange = "A1" that puts A1 intothe variable StartRange. So when Ithink about …………..<o></o>
……I am trying to figure out what causes thequotes to appear around the variables and concatenation…….Is itjust that the Range object throws them in automatically? Maybe I am just over thinkingthis. Don't get me wrong I appreciate that it works. I just find it easier tolearn when I can put into words how things work…..

<o></o>
<o> </o>
Hi Tyron,<o></o>
. It isvery basic stuff, but I agree getting it clear in words just how VBA isthinking is well worth it when learning, especially with the range Methods andProperties. And that info you don’t get from hitting F1 or F2 or from most VBAhelp sites, or at least it is not clearly spelt out in words. ! Great that inForums like this people in the know give the benefit off their Knowledge. <o></o>
. The importantpoint here is

The Range property expects a string expression as it's argument………QUOTE]

.In other words VBA wants to see something in the () or brackets or parenthesesthat has the spreadsheet Format for co ordinates A1 , B1, C3 , $A$1 , $D$7 etc.Anything else causes am error. <o></o>
. Also in VBA (everywhere I think?) double quotes ""defines a string of the value of what you type within the double quotes ""<o></o>
. So this does not work to give you the valuein the first cell in a spreadsheet<o></o>
<o>

Sub RangeBasics1()

MsgBox "First cell value is " & Range(A1).Value

End Sub

</o>
Because A1 is anundefined variable<o></o>
<o> </o>These two do because youhave defined that variable to a string of the form that the Range objectexpects as it's argument.<o></o>
<o> </o>
<o>

Sub Rangebasics2()

Dim A1 As String

Let A1 = "A1"

MsgBox "First cell value is " & Range(A1).Value

End Sub

</o>
<o>

Sub RangeBasics3()

Dim A1 As String

Let A1 = "$A$1"

MsgBox "First cell value is " & Range(A1).Value

End Sub

</o>
<o> </o>
Concatenating is inother words sticking things intimately together (rather than saying linking orjoining which might involve something in between) . & can be thought of asthe VBA Glue.<o></o>
<o> </o>. Things get really complicated if you look atthe VBA evaluate Function. This in it's simplestform allows you to use in VBA the normal spreadsheetFunctions.<o></o>
. So for our simplest example in the spreadsheetyou would have, rather than a message box, this =A1 written in some cell.<o></o>
. The convention / syntax of the Evaluation Functionin VBA is <o></o>
=Evaluate (“ Type_in_here_wot_you_would_after_the= “<o></o>
<o> </o>. So the following works as well for our simpleexample of getting the value of the first cell in the spreadsheet<o></o>
<o>

Sub Rangebasics4()

MsgBox "First cell value is " & Evaluate("A1")

End Sub

</o>

. In a spreadsheet, you can put more than onething in a cell using the spreadsheet & which can be thought of as thespreadsheet glue! <o></o>
. So consider wanting to get the first cell writtentwice. In a Spreadsheet it would be =A1 & A1. But something like thefollowing will not work in VBA<o></o>
<o> </o>Sub Rangebasics5()<o></o>
MsgBox "First cellvalue is " & Evaluate("A1" & "A1")<o></o>
End Sub<o></o>

That is equivalent to =A1A1in the spreadsheet, which Excel will error.<o></o>

. To get the evaluate towork you must apply it to the three things, A1, then the spreadsheet &and finally the second A1. These must be intimately stuck together with the VBA &.<o></o>
So this works<o></o>
<o> </o>Sub Rangebasics6()<o></o>
MsgBox "First cellvalue is " & Evaluate("A1" & "&"& "$A$1")<o></o>
End Sub<o></o>
<o> . </o>Looking at the ThemeRange and Evaluate further: As well as the VBA & , you can also putother VBA stuff in which again must be intimatelystuck together with a VBA & . The syntax it towrite that outside or not in the doublequotes "". For example you can get your first cell string $A$1 using
Range(“A1”).Address ( Note in parsing thatthe .Address property gives the address in the “Dollar” form $A$1 )<o></o>
<o> </o>
Sub Rangebasics7()<o></o>
MsgBox "First cellvalue is " & Evaluate("A1" & "&"& Range("A1").Address)<o></o>
End Sub<o></o>
Or going back to our simple example, an equivalent to SubRangebasics3() is<o></o>
Sub Rangebasics8()<o></o>
MsgBox "First cellvalue is " & Evaluate(Range("A1").Address)<o></o>
End Sub<o></o>
<o> </o>. Just one pen ultimate step before I stopconfusing us all!. I prefer this Format<o></o>
<o> </o>Sub Rangebasics9()<o></o>
MsgBox "First cellvalue is " & Evaluate(" " & Range("A1").Address &" ")<o></o>
End Sub<o></o>
As this helps me in along evaluation line to find out what is going on. Note between the double quotes ""I can, as I have done have any number of spaces, which again I have found helpsme to identify and build up a long evaluation line.<o></o>
. As a last bit. I goback to writing out the cell value twice but as we usually prefer for clarity aseparator is included between them. (I use a _ rather than a – as this helpsprevent the annoying thing of Excel “guessing I am writing a date or time and changingthe format appropriately – another tip for a beginner!). First it is good towrite out the formula in the spread sheet : =A1 & " _" & A1 . Thingsare very tricky here and you really must think what VBA is doing here. Onemight think one has 5 things to evaluate. We sort of do, but although “ _ “ canbe evaluated as in a spreadsheet formula = “ _ “ , the VBA Evaluate syntax following ourdiscussions so far would be would be <o></o>
Evaluate(""- "")<o></o>
And correspondinglythe VBA code<o></o>

Sub Rangebasics10()<o></o>
MsgBox "Firstcell value is " & Evaluate("A1" & "&"& "" - "" &"&" & "A1")<o></o>
End Sub <o></o>

But they do not work.The reason is that VBA in the evaluate syntaxthen reads evaluate nothing and then itsees a _ which it does not understand.<o></o>
So the trick (I think!!) is that immediately after the “&”we are sort of in the Spreadsheet environment. So we immediately after the “&”type “ _ “ and then immediately after this type “&” to get back in the VBA Evaluate environment. Like this<o></o>
Sub Rangebasics11()<o></o>
MsgBox "Firstcell value is " & Evaluate("A1" & "&""- ""&" & "A1")<o></o>
End Sub<o></o>

It seems to work.
<o></o>
. The same again mixingSpreadsheet and VBA bits in an Evaluate thing<o></o>
Sub Rangebasics12()<o></o>
MsgBox "Firstcell value is " & Evaluate("A1" & "&""- ""&" & " " & Range("A1").Address &" ")<o></o>
End Sub<o></o>
. Sorry to have rambled a bit and perhapstaken the thread a bit away from the original point. But it helped me to cleara bit of difficult syntax stuff in my head.

Alan<o></o>

 

[Rick Rothstein]

Hey Andrew Poulsom,

Okay so let me see if I understand correctly.

When I say StartRange = "A1" that puts A1 into the variable StartRange.

The quote marks around "A1" are not part of the text that will be assigned to the StartRange variable.. they are there to tell VB that all the characters between the quotes should be assigned to the variable. Why do it this way? If you did not use quotes around the characters, you would have this...

StartRange = A1

Doing it this way, A1 looks like a variable name to VB, so it would attempt to find a variable by that name and assign it contents to StartRange. To stop VB from seeing A1 as a variable name, you put quotes around it which tells VB that what is between them has no meaning other than as a collection of individual characters.