xldown not working Error: object vairable or with block variable not set

[jzannoni]

Hello,

So here is the setup. I have around 80 sets of data. Each data set is contained in its own tab. I would like to plot all of each data set and then two specific parts of the data set on one smooth continuous no marker scatter plot. The data sets vary in length and so do the partial sections of data. All the data sets are organized the same way in their respective worksheets. The cells to start and end the selected sections of data are specified within a cell in the worksheet (i.e. cell Z2 contains text K273 and so on). The actual piece of code that gets the error is

ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("K3").End(xlDown)

Below is my entire code. I am sure that I will have a similar problem with the next line once this is fixed but it should just be the same problem.

Sub CreateChart()

ActiveSheet.Shapes.AddChart.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Smoothed Stress vs. Strain"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "Whole Test"
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("K3").End(xlDown)
ActiveChart.SeriesCollection(1).Values = ActiveSheet.Range("J3").End(xlDown)
SelectActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "Total Integrated Curve"
ActiveChart.SeriesCollection(2).XValues = ActiveSheet.Range("K3", Range("Y2").Value.Select)
ActiveChart.SeriesCollection(2).Values = ActiveSheet.Range("J3", Range("Y3").Value.Select)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "Brittle Curve"
ActiveChart.SeriesCollection(3).XValues = ActiveSheet.Range("K3", Range("Z2").Value.Select)
ActiveChart.SeriesCollection(3).Values = ActiveSheet.Range("J3", Range("Z3").Value.Select)
End Sub

 

[ismii]

No error on that line with me. It's taking the last value in the K column, correct?

You have lots of errors later, though, when you are using the Range("Y2").value.select, etc. The range object is a range, not selected values.

 

[jzannoni]

I want it to take all the values in the k column. As x values and all the values in the J column as Y values. When I use select for the y2 value that cell is setup to create the end value in k that I want to select so I want to take the string value from it and pass it to the range function. I would like to do the same with y3 z2 and z3. Would just assigning these values to a variable and then calling the variable in the range function be a better way to do this?

 

[ismii]

For getting the x values for the chart, you could use something like:

   ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("K3:K" & ActiveSheet.Range("K3").End(xlDown).Row)

Then similar for Y.

And then for the next, say you have K5 in the cell Y2. You want to have the values for "K3:K5"?

    ActiveChart.SeriesCollection(2).XValues = ActiveSheet.Range("K3:" & ActiveSheet.Range("Y2").Value)

 

[jzannoni]

So this is what my code looks like now.

Sub CreateChart()


ActiveSheet.Shapes.AddChart.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Smoothed Stress vs. Strain"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "Whole Test"
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("K3:K" & ActiveSheet.Range("K3").End(xlDown).Row)
ActiveChart.SeriesCollection(1).YValues = ActiveSheet.Range("J3:J" & ActiveSheet.Range("J3").End(xlDown).Row)
SelectActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "Total Integrated Curve"
ActiveChart.SeriesCollection(2).XValues = ActiveSheet.Range("K3:" & ActiveSheet.Range("Y2").Value)
ActiveChart.SeriesCollection(2).YValues = ActiveSheet.Range("J3:" & ActiveSheet.Range("Y3").Value)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "Brittle Curve"
ActiveChart.SeriesCollection(3).XValues = ActiveSheet.Range("K3:" & ActiveSheet.Range("Z2").Value)
ActiveChart.SeriesCollection(3).YValues = ActiveSheet.Range("J3:" & ActiveSheet.Range("Z3").Value)
End Sub

The values in cells specifying cells are:

Y2=K1261
Y3=J1261
Z2=K1664
Z3=J1664

The total range of J and K are J3:J3605 and K3:K3605

Series 1 in the plot has the correct X values K3:K3605 but the Y values are Y2:Z2
It should have Y values of J3:J3605
Series 2 in the plot has X values Y1:Z1 and Y values of Y3:Z3
It should have X values K3:K1261 and X Values J3:J1261
Series 3 is on the legend but has no input for X and Y values
It should have X values K3:K1644 and Y values J3:J1644

I am still unsure as to why it will not run

 

[jzannoni]

I also get a Runtime Error 438 Object does not support this property or method

So this is what my code looks like now.

Sub CreateChart()


ActiveSheet.Shapes.AddChart.Select
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Smoothed Stress vs. Strain"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "Whole Test"
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("K3:K" & ActiveSheet.Range("K3").End(xlDown).Row)
ActiveChart.SeriesCollection(1).YValues = ActiveSheet.Range("J3:J" & ActiveSheet.Range("J3").End(xlDown).Row)
SelectActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "Total Integrated Curve"
ActiveChart.SeriesCollection(2).XValues = ActiveSheet.Range("K3:" & ActiveSheet.Range("Y2").Value)
ActiveChart.SeriesCollection(2).YValues = ActiveSheet.Range("J3:" & ActiveSheet.Range("Y3").Value)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "Brittle Curve"
ActiveChart.SeriesCollection(3).XValues = ActiveSheet.Range("K3:" & ActiveSheet.Range("Z2").Value)
ActiveChart.SeriesCollection(3).YValues = ActiveSheet.Range("J3:" & ActiveSheet.Range("Z3").Value)
End Sub

The values in cells specifying cells are:

Y2=K1261
Y3=J1261
Z2=K1664
Z3=J1664

The total range of J and K are J3:J3605 and K3:K3605

Series 1 in the plot has the correct X values K3:K3605 but the Y values are Y2:Z2
It should have Y values of J3:J3605
Series 2 in the plot has X values Y1:Z1 and Y values of Y3:Z3
It should have X values K3:K1261 and X Values J3:J1261
Series 3 is on the legend but has no input for X and Y values
It should have X values K3:K1644 and Y values J3:J1644

I am still unsure as to why it will not run

 

[ismii]

XValues for the X-Axis
Values for the Y-Axis. Don't know why it's only "Values" and not "YValues"
(I think you had it proper the first time around for referencing the Y-axis values)

Change that in your code and see if that helps.

I'll check back later if you are still having issues, but maybe tomorrow and not today as to when I'll be able to.

 

[ismii]

Played around a bit and set everything to objects. The code works without doing this, but I like to try to not use "Active" things just in case something actually isn't active when we try to use it.
This will also check to make sure that your Y and Z ranges point to valid ranges, so you can't just have a number or a letter as the value.

Sub CreateChart()


    'Set our sheet
    Dim wksht As Worksheet
    Set wksht = ActiveSheet


    'Make sure all the values we are using are available as we want them on this sheet
    '   Basically just test if we can set a range to the value of the cell. If we can't, _
    '   then it's not a range.
    Dim rngTest As Range
        On Error GoTo ValuesInRangeNotRanges
        Set rngTest = wksht.Range(wksht.Range("Y2").Value)
        Set rngTest = wksht.Range(wksht.Range("Y3").Value)
        Set rngTest = wksht.Range(wksht.Range("Z2").Value)
        Set rngTest = wksht.Range(wksht.Range("Z3").Value)
        On Error GoTo 0
    
    'Create our chart, within the chart object
    Dim ourChart As ChartObject
        'Use the following values to set the specifics of the overall object
        Dim chartWidth As Double: chartWidth = 500
        Dim chartHeight As Double: chartHeight = 250
        Dim chartPositionX As Double: chartPositionX = 200
        Dim chartPositionY As Double: chartPositionY = 100
    Dim chartArea As chart
    
    Set ourChart = wksht.ChartObjects.Add(chartPositionX, chartPositionY, chartWidth, chartHeight)
    Set chartArea = ourChart.chart
    
    'Set the chart specifics for values
    chartArea.HasTitle = True
    chartArea.ChartTitle.Text = "Smoothed Stress vs. Strain"
    chartArea.ChartType = xlXYScatterSmoothNoMarkers


    'Get all of our series on our chart
    chartArea.SeriesCollection.NewSeries
    chartArea.SeriesCollection(1).Name = "Whole Test"
    chartArea.SeriesCollection(1).XValues = wksht.Range("K3:K" & wksht.Range("K3").End(xlDown).Row)
    chartArea.SeriesCollection(1).Values = wksht.Range("J3:J" & wksht.Range("J3").End(xlDown).Row)
    
    chartArea.SeriesCollection.NewSeries
    chartArea.SeriesCollection(2).Name = "Total Integrated Curve"
    chartArea.SeriesCollection(2).XValues = wksht.Range("K3:" & wksht.Range("Y2").Value)
    chartArea.SeriesCollection(2).Values = wksht.Range("J3:" & wksht.Range("Y3").Value)
    
    chartArea.SeriesCollection.NewSeries
    chartArea.SeriesCollection(3).Name = "Brittle Curve"
    chartArea.SeriesCollection(3).XValues = wksht.Range("K3:" & wksht.Range("Z2").Value)
    chartArea.SeriesCollection(3).Values = wksht.Range("J3:" & wksht.Range("Z3").Value)
    
    
CleanExit:
    Exit Sub
    
ValuesInRangeNotRanges:
    MsgBox "Not all of the values specified point to ranges."
    GoTo CleanExit
    
End Sub

We could also use a series object to hold the series we create, but that 'might' be like holding the hand of the code a little more :P.

 

[jzannoni]

That code did exactly what I wanted. Thank you so much for your help!