convert a string to range?
 

Hi folks,

Not a programmer, try to write a macro to refresh a chart base on the
range I choose, say, in the previouse step, set "A1:A1, K1:P1, A5:A5,
K5:P5, A10:A10, K10:P10" to string variable wkrange.
then:

ActiveChart.SetSourceData Source:=Sheets("sheet1").range(wkrange) _
, PlotBy:=xlRows

I get run-time error '1004'

I know something wrong with range(wkrange), would you please shed some
light here?


Jack

convert a string to range?
 

Sub ordinate()
Dim krange As String, r As Range
krange = "A1:A1, K1:P1, A5:A5,K5: P5 , A10: A10 , K10: P10 "
Set r = Range(krange)
End Sub

will definitely convert the string into a range. I know nothing about
charting, however.
--
Gary's Student


"JK" wrote:

Hi folks,

Not a programmer, try to write a macro to refresh a chart base on the
range I choose, say, in the previouse step, set "A1:A1, K1:P1, A5:A5,
K5:P5, A10:A10, K10:P10" to string variable wkrange.
then:

ActiveChart.SetSourceData Source:=Sheets("sheet1").range(wkrange) _
, PlotBy:=xlRows

I get run-time error '1004'

I know something wrong with range(wkrange), would you please shed some
light here?


Jack

convert a string to range?
 

JK wrote...
....
Not a programmer, try to write a macro to refresh a chart base on the
range I choose, say, in the previouse step, set "A1:A1, K1:P1, A5:A5,
K5:P5, A10:A10, K10:P10" to string variable wkrange.
then:

ActiveChart.SetSourceData Source:=Sheets("sheet1").range(wkrange), _
PlotBy:=xlRows

I get run-time error '1004'
....

What are the col A cells supposed to contain? If they're data points
like cols K through P, then the problem is that Excel can only plot
single cells, rows or columns, with rows and columns being single area
ranges. Your rows, (A1:A1,K1:P1), (A5:A5,K5:P5), etc are 2-area ranges,
therefore invalid. You'd need to plot A1:P1, A5:P5, etc.

convert a string to range?
 

Harlan Grove wrote...
....
. . . then the problem is that Excel can only plot
single cells, rows or columns, with rows and columns being single area
ranges. Your rows, (A1:A1,K1:P1), (A5:A5,K5:P5), etc are 2-area ranges,
therefore invalid. You'd need to plot A1:P1, A5:P5, etc.

Responded too quickly. You'd need to name each of the ranges
(A1,K1:P1), (A5,K5:P5) etc., then you'd need to add the NAMED ranges as
separate series to the chart one at a time. You can't use ByRows or
ByColumns when the row or column ranges are multiple area ranges.

convert a string to range?
 

yes,
krange = "A1:A1, K1:P1, A5:A5,K5: P5 , A10: A10 , K10: P10 "
Set r = Range(krange)

it works, now i know my problem is the string is too long, the actual
string looks like:

A4:A4,K4:M4,A5:A5,K5:M5,A6:A6,K6:M6,A7:A7,K7:M7,A8 :A8,K8:M8,A9:A9,K9:M9,A10:A10,K10:M10,A11:A11,K11: M11,A12:A12,K12:M12,A13:A13,K13:M13,A14:A14,K14:M1 4,A15:A15,K15:M15,A16:A16,K16:M16,A17:A17,K17:M17, A18:A18,K18:M18,A19:A19,K19:M19,A20:A20,K20:M20,A2 1:A21,K21:M21,A22:A22,K22:M22,A23:A23,K23:M23,A24: A24,K24:M24,A25:A25,K25:M25,A26:A26,K26:M26,A27:A2 7,K27:M27,A28:A28,K28:M28,A29:A29,K29:M29,A30:A30, K30:M30,A31:A31,K31:M31,A32:A32,K32:M32,A33:A33,K3 3:M33,A34:A34,K34:M34,A35:A35,K35:M35,A36:A36,K36: M36,A37:A37,K37:M37,A38:A38,K38:M38,A39:A39,K39:M3 9,A40:A40,K40:M40

the rows are divisions, the colums are date, what I want to do is to
create a chart base on user's selection { (division1,division2, ...)
(day1, day2,...)}, so it may be row1, row5, row 8,.., and day5 to day9.

Thanks in advance.

JK