Excel VBA setting range from string variable

Question

I'm trying to set a range in a chart series collection from a public variable. The data range is stored in another sheet in the following format. Here is code I have also hard coded in my VBA sub to verify that the range is populating into the variable correctly and then converting to a range. I get the same error either way.

    Public dataRange As String
    dataRange = "MyData!$F$2:$F$118,$H$2:$H$118"

    Dim rgnY As Range

    Set rngY = Range(dataRange)

But I get this error:

    Run-time error '1004' : Method 'Range' of object'_Global' failed

I'm not sure why this is happening. I've googled this error and although I see lots of suggestions I'm not sure how to fix in my case. I need to populate a chart series values and XValues with data stored in this format.

Any ideas on what I'm doing wrong and how to fix it?

Can you try adding the sheet name in front of both ranges?

SJR
– SJR

2017-03-27 21:32:32 +00:00
Commented Mar 27, 2017 at 21:32 — SJR
– SJR, Commented Mar 27, 2017 at 21:32

chris neilsen · Accepted Answer · 2017-03-27 21:38:57Z

3

A Range object can refer to non-contiguous areas, but only where all are on the same sheet. Here you are referring to $F$2:$F$118 on sheet MyData, and $H$2:$H$118 on the Active Sheet, which may or may not be MyData. I'm guessing not since you get an error.

Assuming you intended both sub-ranges to be on MyData use

dataRange = "MyData!$F$2:$F$118,MyData!$H$2:$H$118"

answered Mar 27, 2017 at 21:38

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

1 Comment

Scott Craner Over a year ago

That does get rid of the error, but what is interesting is if you print the address, Debug.Print rgnY.Address(0, 0, 1, 1) even with the MyData! in front of both it returns, [Book1]MyData!F2:F118,H2:H118 Which in turn will return the same error if you try to use it. If it is required why does it not return it that way?

Collectives™ on Stack Overflow

Excel VBA setting range from string variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related