Problem with creating Dynamic Range names in VBA

[ajwillshire]

Hi,

I've put together a routine with a few bits and pieces I found online as well as my own trial and error coding with a copy of John Walkenbach's book beside me and I think it should work.

I have data in a table format starting in A1 with a header row and dates in the first column. I want to create dynamic ranges using the names in the header row and then create a chart. The idea being that if I append data onto the bottom of the table the dynamic ranges and thus the chart will expand.

It seems to work initially - creates the ranges and charts them ok - but if i try and append data then suddenly all the named ranges change to being relative to the cell where I just typed data. And the chart complains about invalid references.

After a few hours trying to fix it, I'm stumped. Any help would be greatly appreciated.

Code (under Excel 2007):


Sub NameandChartTable()
'Routine to take a table of data, give it dynamic range names and chart it.


Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, mySeriesName As String, wksName As String
Dim TopCell As String
Dim BottomCell As String


Range("A1").Select


'Create a new chart - called, coincidentally, "NewChart"

With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
.Chart.ChartType = xlLine
.Name = "NewChart"
End With

' set the row number where headings are held as a constant
' change this to the row number required if not row 1
Const Rowno As Long = 1

' set the Offset as the number of rows below Rowno, where the
' data begins
Const Offset As Long = 1

' set the starting column for the data, in this case 1
' change if the data does not start in column A
Const Colno As Long = 1


Set wb = ActiveWorkbook
Set ws = ActiveSheet
wksName = ws.Name

' count the number of columns used in the row designated to
' have the header names

lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row

For i = Colno To lcol
' if a column header contains spaces, replace the space with an underscore

mySeriesName = Cells(Rowno, i).Value
myName = Replace(mySeriesName, " ", "_")

'Put in the dynamic range formula

TopCell = Cells(Rowno + Offset, i).Address(False, False)

BottomCell = Cells(lrow, i).Address(False, False)

wb.Names.Add Name:=myName, RefersTo:="=offset(" & TopCell & _
", 0, 0, counta(" & TopCell & ":" & BottomCell & "))"


'Assume that when i=1 it is the date column so doesn't need charting
If i > 1 Then
'Put focus on the chart
ActiveSheet.ChartObjects("NewChart").Activate

'Add in each series in the table using the .Formula Property to use the Dynamic Range names
With ActiveChart.SeriesCollection.NewSeries
.ChartType = xlLine
.Name = mySeriesName
.Formula = "=SERIES(" & Chr(34) & mySeriesName & Chr(34) & _
"," & wksName & "!Dates," & wksName & "!" & myName & "," & i & ")"
End With
End If

'Next Column Please!
Next i

ActiveWindow.RangeSelection.Select 'Take the focus off of the chart

End Sub


Thanks, Andrew

 

[ajwillshire]

Hi,

Never mind - figured it out. I wasn't using absolute references!

Instead of Address(False, False) I needed Address(True, True).

D'oh!

Thanks to anyone thinking about this...