X Axis isn't charting properly

Question

I have a macro that charts data for me. Everything about the macro works with 1 exception: I want to redesignate the x-axis labels. The macro does do this but it doesn't do it right. In some cases it frame shifts the categories, in others it labels a category completely wrong. The macro is supposed to chart the data by product ID (one chart per product ID) and then change the x-axis category to the label in column F on "Chart Data."

Thanks, in advance, for your help.

Below is the part of the macro that creates the charts and subsequently changes the x axis:

Sub MakeCharts()
Dim sh As Worksheet
Dim rAllData As Range
Dim rChartData As Range
Dim cl As Range
Dim rwStart As Long, rwCnt As Long
Dim chrt As Chart

Set sh = ActiveSheet
ActiveSheet.Range("a1").Select
With sh

' Get reference to all data
    Set rAllData = .Range(.[A2], .[A2].End(xlDown)).Resize(, 5)
    ' Get reference to first cell in data range
    rwStart = 1
    Set cl = rAllData.Cells(rwStart, 1)
    Do While cl <> ""
        ' cl points to first cell in a station data set
        ' Count rows in current data set
        rwCnt = Application.WorksheetFunction. _
           CountIfs(rAllData.Columns(1), cl.Value)
        ' Get reference to current data set range
        Set rChartData = rAllData.Cells(rwStart, 1).Resize(rwCnt, 5)
        ' Create Chart next to data set
        Set chrt = .Shapes.AddChart(xlLineMarkers, _
           rChartData.Width, .Range(.[A2], cl).Height).Chart
        With chrt
            .SetSourceData Source:=rChartData.Offset(0, 1).Resize(, 4)
            ' -----> Set any chart properties here <-----

            ' Add Title
            .SetElement msoElementChartTitleCenteredOverlay
            .ChartTitle.Caption = cl.Value

            'Change chart name
            .Parent.Name = cl.Value

            'Remove Legend
            .SetElement (msoElementLegendNone)

            ' Adjust plot size to allow for title
            .PlotArea.Height = .PlotArea.Height - .ChartTitle.Height
            .PlotArea.Top = .PlotArea.Top + .ChartTitle.Height

            'Change the x-axis to a more organized set
            .SeriesCollection(1).XValues = "='Chart Data'!$F$2:$F$1048576"

            'Set Max and Min for charts
            .Axes(xlValue).MinimumScale = Sheets("Chart Data").Range("K1")
            .Axes(xlValue).MaximumScale = Sheets("Chart Data").Range("K2")

            'Adjust x-axis to tilt 45 degrees top left to bottom right
            .Axes(xlCategory).TickLabels.Orientation = 45



        End With


        ' Get next data set
        rwStart = rwStart + rwCnt
        Set cl = rAllData.Cells(rwStart, 1)
    Loop

End With

"='Chart Data'!$F$2:$F$1048576" ! you have rwCnt already, so consign the labels to that range: "='Chart Data'!$F$2:$F$" & rwCnt — SeanC
My charts are still not returning the correct x-axis categories but thanks for the pointer anyway, I'm extremely new to vba so I'm not very good at it yet. — nickJR
Hello everyone, I have figured out why the x-axis isn't plotting correctly, I am not just having trouble determining how to fix this. What is happening is: for every product code, it resets the x-axis starting at F1 (I don't understand why it starts at F1, since I have designated F2 as the starting position), and then uses as many rows, in column F, as there are data sets for each product ID. So, for RAND RD002, there are 8 data sets, so it uses F1:F8; likewise there are 12 data sets for RAND RD003 so it uses F1:F12. Thanks again for any help or insight you can offer. — nickJR
so do you need only the data for a particular row in each graph, with A? as the title, B?-E? as the data, all on the same column, and F? as the X-axis label — SeanC
I would need the data from columns A,B,C,D (nothing in column E) but then I want the macro to go back through and replace the x-axis labels, from column B, with the labels in F, to make it look more organized. It's hard to explain without being able to post a sample workbook. I was able to get the macro to label the x-axis correctly with the answer I posted below. Thanks for your help. — nickJR

nickJR nickJR · Accepted Answer · 2013-02-21T15:05:06

under 'Change the x-axis to a more organized set, I used the following code:

.SeriesCollection(1).XValues = "='Chart Data'!" & rChartData.Offset(, 5).Resize(, 1).Address

X Axis isn't charting properly

1 Answers