Chart Range quoting problem

[twinetwstr]

This should be so simple!

I am building a subroutine to create a chart based on a selected group of
cells in separated columns.

If I hard-code the statement as
>>> Set ChrtRange = Range("B5:B8,K5:K8")

I can plug ChrtRange into
>>> CH.Chart.SetSourceData Source:=ChrtRange, PlotBy:=xlColumns
and it works great.

But I want to use variables for the "5", the "8" and the "K". It should be
a matter of building the statement up with concatenation, but I have been
having a terrible time getting it to work.

I have tried to do it the "simple" way with R1C1 values, but the chart that
comes up includes the intermediate rows, like there is a ":" between the
terms instead of a ",".

Here is my best try, but I get error 424 "Object Required" when I try to compile it.

Set ChrtRange = "Range(" & Chr(34) & "B" & clTop & ":B" & clBot & "," & Xcol_
& clTop & XColB & clBot & Chr(34) & ")"


where clTop = 5, clBot = 8, XCol = "K", and XColB = ":K" and I am using
Chr(34) to insert the needed open and close quotes.


I have been playing around with variations for the last hour and a half, and
plead for help

Thanks !

Incidentally, Here are my declarations, and the variables are correctly
interpreted in the string, it just won't assign.
Global clTop As Integer, clBot As Integer
Global CH As ChartObject
Global ChrtRange As Range, Xcol As String
Global XColB As String


Anyone have some thoughts on this?

Van!!

 

[Norie]

You shouldn't need to use Chr(34).

If you are conctenating then you will be creating a string, so no need for the quotes.

 

[twinetwstr]

I tried that first. When I leave the quotes in, the compiler thinks the first quote needed to insert a ":B" closes the quote string and I get a compiler error " :Expected: List Separator or ) "

Then I got clever and initialized a few extra variables to include the appropriate punctuation as
Global XColA As String, XColB As String, XSer As String

Where XColA is ",K", XcolB is "K:" and Xser is ":B" so the code looks like this
>>>> Set ChrtRange = Range(" B & clTop & XSer & clBot & XcolA& clTop & XColB & clBot ")

but now none of the variables are recognized as variables and I still get the 1004 error, "Method of Range object failed"

And if I try to double the quotes so the variables are recognized again, I get that compiler error again.

Set ChrtRange = Range(""B & clTop & XSer & clBot & XcolA & clTop & XColB & clBot "")

There must be another way to enter the range series properly, but I haven't been able to find it.

Still open to suggestions

 

[Norie]

I really think you are overdoing the quotes here, you should not need to double them up.

Why not try concatenating the range to a variable before trying to use it for the chart?

Then you should be able to check that variable to see if it's in the right syntax etc to use.

 

[twinetwstr]

I felt the same way

However, the Range("B5:B8,K5:K8") needs the quotation marks, and I can't find a way to build working variables into the statement that doesn't conflict with the makeup of the statement.

It is much easier to use a Range(Cells(R1,C1),Cells(R2,c2)) but when I do it that way it includes all the intermediate columns in the data series. And theoretically the proper expression would be Range(Cells(R1,C1),Cells(R2,c2),Cells(R3,C3),Cells(R4,C4)) but that gives me a syntax error because the Range statement will not take more than two Cells arguments.

I know there must be something I am missing, but that's why I post it here Thanks for your ideas.

Van!

 

[twinetwstr]

I found the answer on the news.microsoft.com news group. You were actually right, but I was missing some of the formatting. I danced around it but never hit on the magic combination.

Turned out to be this:
Set ChrtRange = Range("B" & clTop & ":B" & clBot & "," & Xcol & clTop & ":" & Xcol & clBot)

Thanks a Bunch for your answer, if I had been a little sharper I would have been able to get it done from your helpful suggestions.

Van!!