Running graph-creating macro through multiple rows

Tshelky

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hey,

I dont and cant use VBA nor any other programming language at all but need this thing for my work, so sorry in advance for my phrasing;

I created a macro simply through "record macro" button in excel. The pourpose of it is to create a simple graph out of one row of a table.

Now I need to somehow make this macro run through every single row of this table and create a graph out of each one of them (the same way that it does for that one specific row that I recorded it for). I found some youtube tutorials on running a macro through rows but none of them focused specifically on graphs.

I am really sorry for asking this, but could someone advice me on how to write a code specifically for graphs? I been looking for this for hours but nothing seems to work at all.

This is how the code looks like currently;

VBA Code:
Sub Likes_Grafy()
'
' Likes_Grafy Makro

'
    Range("B5:J5").Select
    ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range("CZ_data!$B$5:$J$5")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="CZ_grafy"
    ActiveSheet.ChartObjects("Graf 11").Activate
    ActiveSheet.Shapes("Graf 11").IncrementLeft -201.75
    ActiveSheet.Shapes("Graf 11").IncrementTop -81
    ActiveSheet.ChartObjects("Graf 11").Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.FullSeriesCollection(1).XValues = "=CZ_data!$C$3:$J$3"
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 0.2
    Range("L12").Select
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data?
 

Tshelky

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Sure,

changed the data as I cant post the real thing on the internet, but basically this is how the table and chart look like. Is a print screen enough?
 

Attachments

  • excel graf.jpg
    excel graf.jpg
    70 KB · Views: 15
  • excel tabulka.jpg
    excel tabulka.jpg
    143.2 KB · Views: 14

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Try this.
VBA Code:
Sub Likes_Grafy()
Dim wsCharts As Worksheet
Dim wsData As Worksheet
Dim cht As Chart
Dim shp As Shape
Dim rngDst As Range
Dim rngValues As Range
Dim rngXAxis As Range
Dim ser As Series

    Set wsCharts = Sheets("CZ_grafy")
    Set wsData = Sheets("CZ_data")

    Set rngDst = wsCharts.Range("A1")
    Set rngValues = wsData.Range("B5:J5")
    Set rngXAxis = wsData.Range("C3:J3")

    Do

        Set shp = wsData.Shapes.AddChart2(332, xlLineMarkers)

        With shp
            .Left = rngDst.Left
            .Top = rngDst.Top
        End With

        Set cht = shp.Chart

        With cht
            .SetSourceData Source:=rngValues
            .Location Where:=xlLocationAsObject, Name:=wsCharts.Name
            ActiveChart.FullSeriesCollection(1).XValues = rngXAxis.Address(External:=True)
            .Axes(xlValue).MaximumScale = 0.2
        End With



        Set rngValues = rngValues.Offset(1)
        Set rngDst = rngDst.Offset(, 8)

    Loop Until rngValues.Cells(1, 1).Value = ""

End Sub
 

Tshelky

New Member
Joined
Aug 5, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You are a life saver. Thank you so much!
 

Forum statistics

Threads
1,147,995
Messages
5,744,241
Members
423,855
Latest member
successo4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top