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

        '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.Text = chrtname
        'Trying to add x axis labels
        chrt.SeriesCollection(1).XValues = "=Charts Data!$C$3:$O$3"

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, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

You have to change:

chrtname = Cells(i, 2).Value


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

This will work.

answered Jan 23, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Extracting a Variable Number of Letters from a Formula using VBA Code

Please, try the next function: Function extractLetter(rng As ...READ MORE

answered Mar 26, 2023 in Others by Kithuzzz
• 38,010 points
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
• 23,260 points
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
• 63,700 points
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
• 38,010 points
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, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

VBA Loop To Import Changing File Names

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

answered Jan 12, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

How to concatenate elements of a single-dimensional array using VBA?

Using Microsoft 365's UNIQUE and SORT in VBA ' This is a ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,700 points
0 votes
1 answer

See cell content instead of formula in formulabox in Excel using VBA

A VBA Lookup: Lookup Headers in an ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,700 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP