Problems with setting .Values with vba
 

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

I'm using Excel 2000, Windows 7, and writing macros with vba for a Line chart. My workbook has 2 worksheets and 1 Chart Sheet - but I plan to add a sheet for each new year moving forward. A worksheet is active when the macro sub runs. The worksheets are named using a 4-digit 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 set 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 exact 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 that 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 apparently isn't? And having learned that, how DO I build a string from variables 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.

Problems with setting .Values with vba
 

On Thu, 17 May 2012 23:10:36 +0000, confusedXLuser wrote:

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 I started making STUPID mistakes! Thank you so much for an extra set of eyes on this. I would have sworn that the strings in my Watch window were identical!

The core of my problems appear to be my sheet architecture. I need 52 (or 53) elements of data, one for each week of the year. I collapsed a 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 to require split ranges. I tried everything I could - literals, arrays, variables - to set ranges, xValues, or Values, all of which failed. If I use a contiguous range (e.g. $B$4:$B$!) there are no issues. As soon as the syntax changes to something like "$B$4:$B$6, $B$8:$B$10", all 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", which I don't want.

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.

Problems with setting .Values with vba
 

On Fri, 18 May 2012 18:06:55 +0000, confusedXLuser wrote:

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.