named range chart vba

Question

I'm trying to make multiple charts of previously 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, contain 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 like in the date line Set myDateRange = peerGraphDataSht.Range("Graph_Date")? I have 151 named ranges so I would rather not do that.

Joost Joost · Accepted Answer · 2019-01-26T12:17:10

First I would advice you to add Option explicit above your Subroutine, because that would prevent some typo's in the code, like peerGraphDataSht .Cells. It would also prevent undimmed variables, like myNamedRange.

Try to dim myNamedRange as Range and then change the line myNamedRange = NLetters & "_" & NNumber to set myNamedRange = Range(NLetters & "_" & NNumber). Then the range should cover your needs.

named range chart vba

1 Answers