Excel Chart object causes error 1004

[vba317]

I have been trying to learn how to create a chart using vba. I one last thing that I cant figure out. I am trying to build a string to assign the XValue with no luck, I have tried 5 different ways. I used the macro recorder and got an answer that works but I don't understand why it works. Any help would be appreciated.

Tom

Function AddChartSheet(strSheet As String, intRow As Integer, intLastRow As Integer)
    Dim cht As ChartObject
    Dim rng As Range
    Dim intFirstChartRow As Integer
    Dim intLastChartRow As Integer
    Dim strLocation As String
    Dim strDataSource As String
    Dim strSource2 As String
    Dim chtChart As Chart
    Dim strNameSource As String
    
    intFirstChartRow = intLastRow + 3
    intLastChartRow = intFirstChartRow + 25
    strDataSource = "N" & intRow & ":" & "N" & intLastRow
    strLocation = "A" & intFirstChartRow & ":" & "N" & intLastChartRow
    'strNameSource = "=" & strSheet & "!" & "A" & intRow & ":" & "A" & intLastRow 'error 1004
    'strNameSource = strSheet & "!" & "A" & intRow & ":" & "A" & intLastRow 'no error put actual string value on chart
    'strNameSource = " =" & strSheet & "!" & "A" & intRow & ":" & "A" & intLastRow 'no error put actual string value on chart
    'strNameSource = "= " & strSheet & "!" & "A" & intRow & ":" & "A" & intLastRow 'no error put actual string value on chart
    'strNameSource = "=ProTotFY2012!R5C1:R12C1" works
    strNameSource = "=ProTotFY2012!R5C1:R12C1"
    '"=ProTotFY2012!R5C1:R12C1"
          
   Set chtChart = Charts.Add
   Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:=strSheet)
   With chtChart
   'Set the chart type
    .ChartType = xlColumnClustered
    'Set the source data range for the chart.
    .SetSourceData Source:=Sheets(strSheet).Range(strDataSource), PlotBy:= _
        xlColumns
    End With
    With chtChart.Parent
      .Top = Range("A" & intFirstChartRow).Top
      .Width = Range(strLocation).Width
      .Height = Range(strLocation).Height
      .Left = Range("A1").Left
    End With
    'Remove Series Label
    ActiveChart.Legend.Select
    Selection.Delete
    'Add XAxisDataSeries
    ActiveChart.SeriesCollection(1).Select
[Blue]   ActiveChart.SeriesCollection(1).XValues = strNameSource [/Blue]
    Debug.Print strNameSource
    'Set up Font Structure for XValues
    ActiveChart.Axes(xlCategory).Select
    With Selection.TickLabels.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
End Function

 

[vba317]

I figured it out using the range object.

Changing these two lines
strNameSource = "= " & strSheet & "!" & "A" & intRow & ":" & "A" & intLastRow to
to
strNameSource = "A" & intRow & ":" & "A" & intLastRow
and
ActiveChart.SeriesCollection(1).XValues = strNameSource
to
ActiveChart.SeriesCollection(1).XValues = Worksheets(strSheet).Range(strNameSource)

Tom

 

[SkipVought]

Duane sugggested you post in forum707 and I recommended FAQ707-4811.

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}