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 th statement up with concatination, 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"

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
pleed 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!!

will this work for you"

Set ChrtRange = Range("B" & clTop & ":B" & clBot & "," & Xcol & clTop & ":"
& XColB & clBot)

--

Gary Keramidas
Excel 2003


"Claude Van Horn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 th statement up with concatination, 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"
>
> 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 pleed 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!!
>

figured i'd give you the entire code to show how i did it

Sub test()
Dim xcol As String
Dim cltop As Long
Dim clbot As Long
xcol = "K"
cltop = 5
clbot = 8
Set chrtrange = Range("B" & cltop & ":B" & clbot & "," & xcol & cltop & ":"
& _
xcol & clbot)

--

Gary Keramidas
Excel 2003


"Claude Van Horn" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 th statement up with concatination, 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"
>
> 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 pleed 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!!
>

Thanks, Gary, but when I tried it I got a compile error 1004 " Method
'range' of object '_Global' failed" I thought it might have somethig to do
with the fact that I dimensioned the ChrtRange variable as a Golbal, so I
moved things around so the line was in the same subroutine that makes the
chart and dimentioned it as a Local. Then I coppied direct from your post
and formatted it as a single complete line (removing the "-" line split)
Same error. When I debug it, all the variables are correct in the statement
and pop up in the ToolTips and the Locals list, but there's something that
does not work in the range statement itself.

I too am using Excel 2003, so if it worked for you it should work for me. I
had hopes, I hadcommented out all the formats I had tried and yours was a
bit different from the other ones, so I had high hopes!

What now?

"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
news:(E-Mail Removed)...
> figured i'd give you the entire code to show how i did it
>
> Sub test()
> Dim xcol As String
> Dim cltop As Long
> Dim clbot As Long
> xcol = "K"
> cltop = 5
> clbot = 8
> Set chrtrange = Range("B" & cltop & ":B" & clbot & "," & xcol & cltop &
> ":" & _
> xcol & clbot)
>
> --
>
> Gary Keramidas
> Excel 2003
>

Nevermind I found my error. I left the reference to XcolB in the last
term., when I removed the "B" it worked great

See, I told you it had to be my error .... Thanks a LOT!

"Gary Keramidas" <GKeramidasAtMSN.com> wrote in message
news:(E-Mail Removed)...
> figured i'd give you the entire code to show how i did it
>
> Sub test()
> Dim xcol As String
> Dim cltop As Long
> Dim clbot As Long
> xcol = "K"
> cltop = 5
> clbot = 8
> Set chrtrange = Range("B" & cltop & ":B" & clbot & "," & xcol & cltop &
> ":" & _
> xcol & clbot)
>