CODEWITHSUNDEEP
vbaexcel
Jane
Jane

Reputation: 11

VBA Chart x-axis: why is it row no. rather than values?

I'm trying to create a chart on excel using VBA. However the x-axis keeps showing row number rather than the value in the row. Here is the code that I currently have. Am I doing something wrong? The y-axis is correct though

If CheckBox4.Value = False Then
If CheckBox1.Value = True Then 'S11 S22
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Text = "S11 & S22"
        .ChartType = xlXYScatterLinesNoMarkers
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Frequency/GHz"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "/dB"
        .SetSourceData Source:=Range("B2:T5")

        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop

        .SeriesCollection.NewSeries
        .SeriesCollection(1).Name = "S11"
        .SeriesCollection(1).XValues = Range("A9:A" & j)
        .SeriesCollection(1).Values = Range("B9:B" & j)
            .SetElement (msoElementPrimaryCategoryGridLinesMinorMajor)
            .SetElement (msoElementPrimaryValueGridLinesMinorMajor)
         '   .SetElement (msoElementPrimaryCategoryAxisLogScale)
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Name = "S22"
        .SeriesCollection(2).XValues = Range("A9:A" & j)
        .SeriesCollection(2).Values = Range("H9:H" & j)
        With .Parent
            .Height = 300
            .Width = 500
            .Top = 50
            .Left = 600
        End With
    End With
End If

Thank you in advance guys!

Upvotes: 0

Views: 286

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

Try replcaing 

.SeriesCollection(1).XValues = Range("A9:A" & j)

with:

.SeriesCollection(1).XValues = "=" & Range("A9:A" & j).Address(False, False, xlA1, xlExternal)

Upvotes: 1

Related Questions