PDA

View Full Version : Solved: Using named range to plot chart data



leal72
08-31-2009, 07:08 AM
I am trying to write code for the Macro to use a named range ("Load" and "Disp") to plot a series onto a chart.

I tried using .Range("E:E") but the graph is off because a couple of the cells that get selected have text

with the code below I get the

Run-time error '1004':
Application-defined or object-defined error

where it is in bold.


With ActiveSheet
' cell formatting here!

' find last row with data
LastRow = .Range("A65536").End(xlUp).Row
DataStart = FindTime + 2

For x = DataStart To LastRow
' more cell formattting and calclations here!
Next x

Range("E" & DataStart, ActiveCell.End(xlDown)).Name = "Load"

Range("F" & DataStart, ActiveCell.End(xlDown)).Name = "Disp"

End With

' plot chart data for each test specimen
Sheets("SummaryData").Select
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.SeriesCollection.NewSeries
.Name = WsName
.Values = Sheets(WsName).Range("Load")
.XValues = Sheets(WsName).Range("Disp")
End With

leal72
08-31-2009, 01:13 PM
I got this figured out, thanks.

mdmackillop
09-01-2009, 12:34 PM
Can you post your solution for the benefit of others who read this.

leal72
09-01-2009, 12:57 PM
here is how I was able to get it to do what I wanted. Basically told VBA to select the first cell before using the control down function. The way it ran before it was selecting columns A through E which is what was giving me the error. Hope that makes sense.

Range("E" & DataStart).Select

Range("E" & DataStart, Selection.End(xlDown)).Select

Selection.Name = "Load"

Range("F" & DataStart).Select

Range("F" & DataStart, Selection.End(xlDown)).Select

Selection.Name = "Disp"

mdmackillop
09-01-2009, 04:38 PM
Try to avoid Selection to speed up your code


Dim rng As Range
Set rng = Range("E" & datastart)
Range(rng, rng.End(xlDown)).Name = "Load"
Set rng = Range("F" & datastart)
Range(rng, rng.End(xlDown)).Name = "Disp"

leal72
09-11-2009, 08:07 AM
Finally got a chance to put this in, Thank you, works great!