.Values / .XValues modification

[baconcow]

I am trying to modify the following code from...

' .Values = "='2.1 - Surface - Solution'!$E$5:$E$12"
' .XValues = "='2.1 - Surface - Solution'!$B$5:$B$12"

To...

.Values = Worksheets("2.1 - Surface - Solution").Range("E5",
Range("E5").Offset(0, end_21.Value))
.XValues = Worksheets("2.1 - Surface - Solution").Range("E5",
Range("E5").Offset(0, end_21.Value))


Where (these are the first things):

Dim end_21 as Range
Set end_21 = Worksheets("2.1 - Surface - Solution").Range("M1")
.... and range M1 has a value of 8 in it

It gives me a application or object-defined error. I am not sure how to
modify the .Values and .XValues.

Thanks

 

[Jon Peltier]

Try this:

With Worksheets("2.1 - Surface - Solution")
.Values = .Range(.Range("E5"), .Range("E5").Offset(0, end_21.Value))
.Values = .Range(.Range("B5"), .Range("B5").Offset(0, end_21.Value))
End With

If the series is an XY or Line series, and there is not plottable data in
the series before the code is run, it will give an error anyway. If that
occurs, try changing the series temporarily to an area or column type prior
to changing the data.

- Jon

 

[baconcow]

I just realized that I left out a lot of code that might have been helpful to
you. The original code is not useful since it is static and cannot be
modified to account for having a range end that can change. Thanks for the
help, so far! This is currently what I have:


'create and setup chart
Dim ch211 As Chart
Set ch211 = ActiveWorkbook.Charts.Add
ch211.Name = "2.1.1 - Temperature"
ch211.SetSourceData Source:=Worksheets("2.1 - Surface").Range("C5:E21"), _
PlotBy:=xlColumns

' set chart title
ch211.HasTitle = True
ch211.ChartTitle.Text = "Temperature"

Dim end_21 As Range
Set end_21 = Worksheets("2.1 - Surface").Range("M1")

' setup the chart labels, graph types, and series
With ch211.SeriesCollection(1)
.Name = "Actual"
.Values = .Range(.Range("E5"), .Range("E5").Offset(0, end_21.Value))
.XValues = .Range(.Range("B5"), .Range("B5").Offset(0, end_21.Value))
.ChartType = xlColumnClustered
.Border.Color = RGB(31, 73, 125) 'dark blue, text 2
.Interior.Color = RGB(31, 73, 125) 'dark blue, text 2
End With

 

[baconcow]

I have recently tried the following code within there...

..Values = Worksheets("2.1 - Surface - Solution") _
.Range(Range("E5"), Range("E12"))

I placed the Range("E12") to simplify (not permanent) the code from the one
with the offset, to show what I want to accomplish. I do not think the with
is possible since the .Values is already with the
"ch211.SeriesCollection(1)". Just my work in progress... Thanks again.

 

[baconcow]

Using the code and idea I found GOOGLE'ing another forum, I ended up getting
it to work. It seems I was missing the worksheet object at the beginning of
each inner range object.

Source: http://bytes.com/forum/thread15008.html


Dim WS As Worksheet
Dim end_21 As Long
Set WS = Worksheets("2.1 - Surface")
end_21 = WS.Range("M1").Value

' setup the chart labels, graph types, and series
With ch211.SeriesCollection(1)
.Name = "Actual"
.Values = WS.Range(WS.Range("E5"), WS.Range("E5").Offset(end_21))
.XValues = WS.Range(WS.Range("B5"), WS.Range("B5").Offset(end_21))
.ChartType = xlColumnClustered
.Border.Color = RGB(31, 73, 125) 'dark blue, text 2
.Interior.Color = RGB(31, 73, 125) 'dark blue, text 2
End With


Thanks again for your time and help, Jon.

Shawn