Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Variables to a range with quotes
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Variables to a range with quotes
will this work for you"
Set ChrtRange = Range("B" & clTop & ":B" & clBot & "," & Xcol & clTop & ":" & XColB & clBot) -- Gary Keramidas Excel 2003 "Claude Van Horn" wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Variables to a range with quotes
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" wrote in message ... 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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Variables to a range with quotes
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 ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding Variables to a range with quotes
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 ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup is adding quotes! | Excel Worksheet Functions | |||
Adding a range with three variables | Excel Discussion (Misc queries) | |||
Adding in a range with multiple variables | Excel Discussion (Misc queries) | |||
my code is adding single quotes | Excel Programming | |||
Adding quotes to columns | Excel Discussion (Misc queries) |