Convert string to range?

[k]

I have the following variables defined.

startxrange = Rng.Offset(2, 0).Address
startyrange = Rng.Offset(3, 0).Address
endxrange = Range(tempendxrange).Address
endyrange = Range(endxrange).Offset(1, 0).Address

Each of these has a value such as $A$2. I would like to combine the two
x-ranges together and the two y-ranges together and plug them into a later
part of my code that will use said ranges to perform calculations. Here is
what I tried to combine them together, using the x-range as an example.

Dim XRates As Range
XRates = startxrange & ":" & endxrange
MsgBox "XRates is " & XRates

Then my function performs this on the constructed range. Of course, this is
where my macro fails.

Count = XRates.Count

A newsgroup search I performed yielded one post that seemed to suggest using
Indirect, although I'm not sure how to work it into this example and if
that'd be recommended.

Any assistance as to what I'm doing wrong would be appreciated. Thanks in
advance.

k

 

[Leo Heuser]

If I have understood you correctly, try this:

Dim XRates As Range
Dim CountCells As Long

Set XRates = Range(startxrange & ":" & endxrange)
MsgBox "XRates is " & XRates.Address

CountCells = XRates.Cells.Count

I'm not sure, if the last line is what
you're after, but Count is a built-in name,
so avoid using it as a variable name.

 

[JE McGimpsey]

Ranges are objects, so you need to use the Set statement to assign a
variable to them:


Dim XRates As Range
Set XRates = Range(startxrange & ":" & endxrange)
MsgBox "XRates is " & XRates.Address

or

Dim XRates As Range
Set XRates = Range(Rng.Offset(2, 0), Range(tempendxrange))

 

[k]

Thanks for both responses; they solved my problem. As you can tell, I'm not
much of a VBA programmer, but I'd be much farther behind if not for this
newsgroup. It's an amazing resource powered by helpful & knowledgeable
individuals. Thanks again.

k

 

[Leo Heuser]

You're welcome "k".
We're glad to be able to help

LeoH