CODEWITHSUNDEEP
vbaexcel
Eeshwar
Eeshwar

Reputation: 83

Excel-VBA: Error 438 on Adding Values to Series in Chart Sheet, Excel 2010

I'm trying to put together some code that collects a set of data from a worksheet and then generates an XY Scatter plot. Whenever it reaches the line for inputting values into the series, it produces a "Runtime Error 438: Object doesn't support this property or method."

xDataRng.Address has a value of "$C$8:$C$11"

I'm using Excel 2010 on Win 7.

I've looked up every article and help thread I could find about this issue. Am I using .SeriesCollection.XValues incorrectly? How can I fix it?

Thanks,

Eeshwar

Sub createChart()

Set wb = ThisWorkbook
Dim sheetName As Variant, chartName As Variant

sheetName = ActiveSheet.Name

'Find x-axis data
Dim xnameRng As Range, xdataRng As Range
Dim lastCol As Long, lastRow As Long
Dim i As Integer

With ActiveSheet
    lastCol = .Cells(7, .Columns.Count).End(xlToLeft).Column
    Set xnameRng = .Range(Cells(7, 2), Cells(7, lastCol)).Find("Horizontal Position (", lookat:=xlPart)
    lastRow = .Cells(.Rows.Count, xnameRng.Column).End(xlUp).Row
    Set xdataRng = .Range(xnameRng.Offset(1, 0).Address, Cells(lastRow, xnameRng.Column))
End With

'Find y-axis data
Dim ynameRng As Range, ydataRng As Range

With ActiveSheet
    Set ynameRng = .Range(.Cells(7, 2), .Cells(7, lastCol)).Find("Pressure (", lookat:=xlPart)
    Set ydataRng = .Range(ynameRng.Offset(1, 0).Address, .Cells(lastRow, ynameRng.Column))
End With

'Create chart
With wb.Sheets("Chart_Template")
    .Copy After:=Sheets(sheetName)

    chartName = ActiveChart.Name

    'Update chart details
    With wb.Sheets(chartName)
        .SeriesCollection.NewSeries
        .SeriesCollection.XValues(1) = wb.Sheets(sheetName).Range(xdataRng.Address)  FAILS HERE
        .SeriesCollection.Values(1) = wb.Sheets(sheetName).Range(ydataRng.Address)
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).Character.Text = xnameRng.Value
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).Character.Text = ynameRng.Value
    End With
End With

End Sub

Upvotes: 2

Views: 779

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

Try the section of code below to replace yours:

With wb.Sheets(chartName)
    Dim Ser As Series

    Set Ser = .SeriesCollection.NewSeries
    With Ser
        .XValues = "=" & xDataRng.Address(True, True, xlA1, xlExternal)
        .Values = "=" & yDataRng.Address(True, True, xlA1, xlExternal)
    End With

    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).Character.Text = xnameRng.Value
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).Character.Text = ynameRng.Value
End With

Upvotes: 2

Related Questions