Problems with setting .Values with vba

[confusedXLuser]

I've spent 2 weeks banging my head against the wall, and coming up wit
"workarounds" to every roadblock rather than solutions. I've finall
reached an impasse.

I'm using Excel 2000, Windows 7, and writing macros with vba for a Lin
chart. My workbook has 2 worksheets and 1 Chart Sheet - but I plan t
add a sheet for each new year moving forward. A worksheet is activ
when the macro sub runs. The worksheets are named using a 4-digi
year.

I am clueless as to why the following lines of code are not the same
but the first set works fine (but doesn't help) and the second se
throws a '1004' exception.

Set-up:
Dim MyYear as String
Dim RangeStr as String

MyYear = ActiveSheet.Name 'for example, "2012"

The following 2 lines work fine
RangeStr = "=('2012'!R10C2:R10C27,'2012'!R34C2:R34C28)"
Charts("Chart").SeriesCollection(1).Values = RangeStr

The following two lines set RangeStr to what appears to be the exac
same text, but the line that sets .Values barfs with a '1004' error
(Please forgive the fact that I did NOT clean up the line of code tha
overruns the margins for this posting.)
RangeStr = "=('" & MyYear & "'!R10C2:R10C27,'" & MyYear
"'!R34C2:R34C28"
Charts("Chart").SeriesCollection(1).Values = RangeStr

Can anyone tell me why what appears to be the same string apparentl
isn't? And having learned that, how DO I build a string from variable
to set the .Values parameter?

What I've tried:
1 - I tried doing the 2nd set of code lines first, to no avail.
2 - I tried changing the chart type before making the assignment

TIA for any help you can render

 

[Ron Rosenfeld]

Can anyone tell me why what appears to be the same string apparently
isn't?

Your second string is missing a right parenthesis:

RangeStr = "=('2012'!R10C2:R10C27,'2012'!R34C2:R34C28)"
--> =('2012'!R10C2:R10C27,'2012'!R34C2:R34C28)

RangeStr = "=('" & MyYear & "'!R10C2:R10C27,'" & MyYear & "'!R34C2:R34C28"
--> =('2012'!R10C2:R10C27,'2012'!R34C2:R34C28

 

[confusedXLuser]

;1601872']On Thu, 17 May 2012 23:10:36 +0000, confusedXLuse

Can anyone tell me why what appears to be the same string apparently
isn't? -

Your second string is missing a right parenthesis:

RangeStr = "=('2012'!R10C2:R10C27,'2012'!R34C2:R34C28)"
--> =('2012'!R10C2:R10C27,'2012'!R34C2:R34C28)

RangeStr = "=('" & MyYear & "'!R10C2:R10C27,'" & MyYear
"'!R34C2:R34C28"
--> =('2012'!R10C2:R10C27,'2012'!R34C2:R34C28

OH NOOOOO!
I am SOOO ashamed! I obviously reached a level of frustration where
started making STUPID mistakes! Thank you so much for an extra set o
eyes on this. I would have sworn that the strings in my Watch windo
were identical!

The core of my problems appear to be my sheet architecture. I need 5
(or 53) elements of data, one for each week of the year. I collapsed
sheet that was 53 elements wide into two that were 26 and 26 (or 27)
fitting all the data on a single screen. Of course, this caused me t
require split ranges. I tried everything I could - literals, arrays
variables - to set ranges, xValues, or Values, all of which failed. I
I use a contiguous range (e.g. $B$4:$B$!) there are no issues. As soo
as the syntax changes to something like "$B$4:$B$6, $B$8:$B$10", al
heck breaks loose! I can get rid of errors using a different syntax
($B$4:$B$6","B$8:$B$10"), but this is equivalent to "$B$4:$B$10", whic
I don't want.

If I can't figure out how to use split ranges on a Line chart in th
next couple days, I'll go into 'workaround' mode and try a hidden shee
with contiguous ranges - copying my split-sheet data to it. Not m
preference, but much less complex from a vba coding perspective. I'v
already wasted 2 weeks trying every web-based idea I could find.

Thanks again for your quick response, and I close begging you
forgiveness for wasting your time on such a dumb mistake

 

[Ron Rosenfeld]

If I can't figure out how to use split ranges on a Line chart in the
next couple days, I'll go into 'workaround' mode and try a hidden sheet
with contiguous ranges - copying my split-sheet data to it. Not my
preference, but much less complex from a vba coding perspective. I've
already wasted 2 weeks trying every web-based idea I could find.

Thanks again for your quick response, and I close begging your
forgiveness for wasting your time on such a dumb mistake.

Glad to help. And I've made plenty of mistakes which, when uncovered, seemed pretty dumb.