CODEWITHSUNDEEP
vbaexcelloopsrange
Marco
Marco

Reputation: 19

Update: Loop through several ranges?

UPDATE: I slightly modified the code suggested by Nick. Originally, this code with the Rng.Offset took big ranges of data to make the Radar Charts. For instance: From (A1:E1):(A2:E2) (this first is fine), but then it went: (A1:E1):(A3:E3), (A1:E1):(A4:E4), (A1:E1):(A5:E5), and so on... Instead I am trying to select just one additional row each time, thus: (A1:E1)+(A3:E3), (A1:E1)+(A4:E4), (A1:E1)+(A5:E5).

Sub MultipleRadarCharts()

Dim i As Integer
Dim Rng As Range

Set Rng = Sheets("Indices4Pilares (4)").Range("A1:E1")

For i = 2 To 36
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlRadarMarkers
   'ActiveChart.SetSourceData Source:=Sheets("Indices4Pilares (4)").Range(Rng, Rng.Offset(i - 1, 0))
   'ActiveChart.SetSourceData Source:=Sheets("Indices4Pilares (4)").Range(Rng, Range(Cells(i, 1), Cells(i, 5)))
    ActiveChart.SetSourceData Source:=Sheets("Indices4Pilares (4)").Union(Range(Cells(1, 1), Cells(1, 5)), Range(Cells(i, 1), Cells(i, 5))).Select
    ActiveChart.Legend.Delete
Next i
Set Rng = Nothing
End Sub

=====

:) I've just created this code in order to get radar charts from an Excel table.

The first row has the name of the variables, and then (from the 2nd to the 36th row) there are values. The issue is that I am stuck trying to simplify the code with a "for loop", but I cannot make it work :(

This is the raw code I want to simplify...

Sub Macro2()

Range("A1:E1,A2:E2").Select
Range("A2").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlRadarMarkers
ActiveChart.SetSourceData Source:=Range( _
    "'Indices4Pilares (3)'!$A$1:$E$1,'Indices4Pilares (3)'!$A$2:$E$2")
ActiveChart.Legend.Select
Selection.Delete

Range("A1:E1,A3:E3").Select
Range("A3").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlRadarMarkers
ActiveChart.SetSourceData Source:=Range( _
    "'Indices4Pilares (3)'!$A$1:$E$1,'Indices4Pilares (3)'!$A$3:$E$3")
ActiveChart.Legend.Select
Selection.Delete

Range("A1:E1,A4:E4").Select
Range("A4").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlRadarMarkers
ActiveChart.SetSourceData Source:=Range( _
    "'Indices4Pilares (3)'!$A$1:$E$1,'Indices4Pilares (3)'!$A$4:$E$4")
ActiveChart.Legend.Select
Selection.Delete

Range("A1:E1,A5:E5").Select
Range("A5").Activate
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlRadarMarkers
ActiveChart.SetSourceData Source:=Range( _
    "'Indices4Pilares (3)'!$A$1:$E$1,'Indices4Pilares (3)'!$A$5:$E$5")
ActiveChart.Legend.Select
Selection.Delete
End Sub

Upvotes: 1

Views: 97

Answers (1)

Nick Peranzi
Nick Peranzi

Reputation: 1375

Try the below code:

Sub MultipleRadarCharts()

Dim i As Integer
Dim Cht As Chart
Dim Rng As Range

Set Rng = Sheets("Indices4Pilares (3)").Range("A1:E1")

For i = 2 To 36
    Set Cht = ActiveSheet.Shapes.AddChart
    Cht.ChartType = xlRadarMarkers
    'Cht.SetSourceData Source:=Sheets("Indices4Pilares (3)").Range(Rng, Rng.Offset(0, i - 1)) 'Old solution
    Cht.SetSourceData Source:=Sheets("Indices4Pilares (3)").Range(""Rng.Address & "," & Rng.Offset(0, i - 1).Address"") 'New solution
    Cht.Legend.Delete
    Set Cht = Nothing
Next i

Set Rng = Nothing

End Sub

Upvotes: 1

Related Questions