Adding existing Named ranges to chart in a loop VBA

[Svalle]

Hi

I'm trying to make multiple charts of previous created named ranges, but i simply can't get the VBA code to work when referring to a cell that contains the name of the Named Range.

It looks like this:

Sub Top25_Charts()

Sheets("Top25Graphs").Activate
Set peerDataSht = ThisWorkbook.Worksheets("Top25History")
Set peerGraphSht = ThisWorkbook.Worksheets("Top25Graphs")
Set peerGraphDataSht = ThisWorkbook.Worksheets("Top25GraphsData")




Dim myDateRange As Range
'Dim myNamedRange As Range
Dim cht As Object
Dim NumberPeers As Integer
Dim NLetters As String
Dim NNumber As Integer


Application.ScreenUpdating = False


For i = 1 To 1


NumberPeers = peerDataSht.Cells(i + 30, 13)
NLetters = peerGraphDataSht .Cells(i + 30, 1)
CName = peerDataSht.Cells(i + 61, 2)
Set cht = peerGraphSht.Shapes.AddChart2.Chart
Set myDateRange = peerGraphDataSht.Range("Graph_Date")


With cht
.ChartType = xlXYScatterLinesNoMarkers
.HasTitle = True
.ChartTitle.Text = CName
.HasLegend = True
.Legend.Position = xlBottom


End With

For j = 1 To NumberPeers


NNumber = peerGraphDataSht .Cells(30, 1 + j)
num = peerDataSht.Cells(31, 1 + j)
PeerName = peerDataSht.Cells(61 + i, 1 + j)
'peerDataSht.Range(Cells(i + 30, 1 + j))
myNamedRange = NLetters & "_" & NNumber


With cht.SeriesCollection.NewSeries
.XValues = myDateRange
.Values = NLetters & "_" & NNumber
.Name = PeerName
End With

Next j


With cht.Parent
.Left = peerGraphSht.Range("B2").Left
.Top = peerGraphSht.Range("B2").Top
.Width = peerGraphSht.Range("B2:I2").Width
.Height = peerGraphSht.Range("B2:B16").Height
End With


Next i

Application.ScreenUpdating = True


End Sub

But i can't refer to NLetters and NNumber.
Two references NLetters and Nnumber contains the named ranges, so the named ranges are called a_1, a_2 .... z_6

The line 'peerDataSht.Range(Cells(i + 30, 1 + j)) refers directly to the cell that contains the named range, but also can't be used.

So is it possible to refer to a cell that contains the name of the named range, or do i have to insert the named range directly in the code (i have 151 named ranges so i rather not do that) like in the date line Set myDateRange = peerGraphDataSht.Range("Graph_Date")

 

[pgc01]

Hi

See if this helps

This code adds a chart to Sheet1.
Uses the name "YValues1" for the yvalues.

Run the code in a a new workbook.

Sub AddChartFromNamedRange()
Dim ws As Worksheet
Dim rYValues As Range ' yvalues of the chart series
Dim rChtLoc As Range ' location and dimensions of the chart
Dim chtO As ChartObject
Dim nm As Name

Set ws = Worksheets("Sheet1") ' will create the chart in this worksheet

With ws
    Set rChtLoc = .Range("F2:M15")
    Set rYValues = .Range("A2:C2")
    rYValues.Value = Array(2, 5, 3) ' initialise the yvalues for testing
    Set nm = Names.Add(Name:="YValues1", RefersTo:=rYValues)
End With

With rChtLoc
    Set chtO = ws.ChartObjects.Add(.Left, .Top, .Width, .Height)
End With

With chtO.Chart
    With .SeriesCollection.NewSeries
        .Values = ws.Range("YValues1") ' sets the yvalues from the name
    End With
End With
End Sub