Adding Variables to a range with quotes

[Claude Van Horn]

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

 

[Gary Keramidas]

will this work for you"

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

 

[Gary Keramidas]

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)

 

[Claude Van Horn]

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?

 

[Claude Van Horn]

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!