convert a string to range?

[JK]

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, K11, A5:A5,
K55, A10:A10, K1010" 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

 

[Guest]

Sub ordinate()
Dim krange As String, r As Range
krange = "A1:A1, K11, 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.

 

[Harlan Grove]

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, K11, A5:A5,
K55, A10:A10, K1010" 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,K11), (A5:A5,K55), etc are 2-area ranges,
therefore invalid. You'd need to plot A11, A55, etc.

 

[Harlan Grove]

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,K11), (A5:A5,K55), etc are 2-area ranges,
therefore invalid. You'd need to plot A11, A55, etc.

Responded too quickly. You'd need to name each of the ranges
(A1,K11), (A5,K55) 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.

 

[JK]

yes,
krange = "A1:A1, K11, 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:M14,A15:A15,K15:M15,A16:A16,K16:M16,A17:A17,K17:M17,A18:A18,K18:M18,A19:A19,K19:M19,A20:A20,K20:M20,A21:A21,K21:M21,A22:A22,K22:M22,A23:A23,K23:M23,A24:A24,K24:M24,A25:A25,K25:M25,A26:A26,K26:M26,A27:A27,K27:M27,A28:A28,K28:M28,A29:A29,K29:M29,A30:A30,K30:M30,A31:A31,K31:M31,A32:A32,K32:M32,A33:A33,K33:M33,A34:A34,K34:M34,A35:A35,K35:M35,A36:A36,K36:M36,A37:A37,K37:M37,A38:A38,K38:M38,A39:A39,K39:M39,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