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

 

[DocAElstein]

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.

Hi Rick,
. I think that is one 0f the things I was trying to say
....

. 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>

But I think you explained it a bit more concisely,
Thanks
Alan

 

[Tyron]

Hey Guys,

Thanks to all of you for clarifying the situation.

Andrew: Yeah, good catch on my mistake. I did mean to put quotes not parenthesis. Sorry about that.

Doc: I started to get a little confused, but it helped me understand Andrew's two posts better when I went back to the top and started reading the thread from the begining.

Rick: The information you gave me regarding why VBA needs quotes in order to distinguish between Values and Variables also opened my eyes a bit more as well.

So this subject is definitely a lot clearer and has helped me to understand why Range works the way it does.

Thanks to all again.

later

Ty