Automating production of graphs using VBA

0 votes

I need to make a lot of graphs and am attempting to avoid manually developing and formatting them. Data For data protection, I have removed the row labels from an image of the data in the previous section. For each time series row of data, I need a line graph, with the title of each graph being the label for the row in the leftmost column and the x axis labels being the labels for the columns. I can make graphs using a for loop, but I'm having trouble assigning labels to the x axis and chart titles. I've read other threads, but the fixes don't seem to be working. Here is the code as of now:

Sub main()

    Dim i As Long
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim chrt As Chart
    Dim chrtname As String

    'Find the last used row
    'LastRow = Sheets("Chart Data").Range("A1").End(xlUp).Row
    LastRow = 272
    'Find the last used column
    'LastColumn = Sheets("Chart Data").Range("A1").End(xlToRight).Column
    LastColumn = 15
    'Looping from second row till last row which has the data
    For i = 86 To LastRow
        Sheets("Sheet2").Select

        'Adds chart to the sheet
        Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart
        'sets the chart type
        chrt.ChartType = xlLine

        'adding line chart
        With Sheets("Charts Data")
            chrt.SetSourceData Source:=.Range(.Cells(i, 3), .Cells(i, LastColumn))
        End With

        'adjust the position of chart on sheet
        chrt.ChartArea.Left = 1
        chrt.ChartArea.Top = (i - (i *0.5)) * chrt.ChartArea.Height
        
        'Trying to set a chart title
        chrt.HasTitle = True
        chrtname = Cells(i, 2).Value
        chrt.SetElement (msoElementChartTitleAboveChart)
        chrt.ChartTitle.Select
        chrt.ChartTitle.Text = chrtname
        
        'Trying to add x axis labels
        chrt.SeriesCollection(1).XValues = "=Charts Data!$C$3:$O$3"
    
    
        Next

End Sub

The chart title appears to get deleted when trying to assign a cell value in all other cases, which is a similar issue to what one other user seemed to be experiencing. How to use VBA to make a cell value the title of a chart

Any advice would be appreciated because this was resolved confidentially. I also need to include an additional data series that is consistent across all charts, but I haven't been able to do that either.

I'm sorry if this is a very simple question, but I'm new to VBA.

Jan 23 in Others by Kithuzzz
• 27,740 points
35 views

1 answer to this question.

0 votes

You have to change:

chrtname = Cells(i, 2).Value

To:

chrtname = Sheets("Charts Data").Cells(i, 2).Value

This will work.

answered Jan 23 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
274 views
0 votes
1 answer

How to modify Powerpoint Chart ChartData by Excel VBA

Example: Code: Set pptApp = GetObject(, "PowerPoint.Application") Set pptPres = ...READ MORE

answered Oct 16, 2022 in Others by narikkadan
• 51,240 points
397 views
0 votes
0 answers

xcel VBA Chart counting and formatting

I want to develop a macro that ...READ MORE

Oct 31, 2022 in Others by Kithuzzz
• 27,740 points
52 views
0 votes
1 answer

Name for excel graph problem with the vba generator

Change the name of the workbook : Sub Macro_name_graph2() ...READ MORE

answered Jan 7 in Others by narikkadan
• 51,240 points
45 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12 in Others by narikkadan
• 51,240 points
44 views
0 votes
1 answer

array of LocalBusinessJsonLd Schema using next-seo

specify the unique keyoverride properties if you ...READ MORE

answered Feb 14, 2022 in Others by narikkadan
• 51,240 points
420 views
0 votes
1 answer

array of LocalBusinessJsonLd Schema using next-seo

" If your page requires multiple instances of ...READ MORE

answered Feb 24, 2022 in Others by narikkadan
• 51,240 points
155 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP