Macros not recognising moved information

[Spook E]

In my spreadsheet i have an area for allowing users to enter survey results, at the end of which is a button they can push (with an attached macro) that will display the results of the survey in a pie graph.

The information used by the macro to show the series information is stored in columns at the end of the end of the data entry area

All works well until extra columns are entered in the data entry area, pushing the category label information further across the spreadsheet. As the macro uses this data to display graph category label content, the macro needs to recognise that the data range for this has moved and to move with it (i.e F3:F7 becomes G3:G7). The macro is not picking this change up, and when the button is pressed to generate the graph, the category label information is missed.

How do i make the macro realise that this important information has moved and to ensure it is captured.

Image added, to hopefully make sense of my description

 

[Derek]

Hi there

Name the range, then refer to it by its name.
eg
Select the range then go to Insert / Name / Define and name it "LOC"

In your macro refer to it as Range("LOC")

regards
Derek

 

[Spook E]

I tried that, but when i enter the range name in the graph wizard it gives me an error saying that the Range("jeff") is invalid.

 

[Andrew Poulsom]

Please post your macro.

 

[Spook E]

Sub f10chart()
'
' f10chart Macro
' Macro recorded 18/08/2006 by dbadcock
'

'
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Residential Family Survey").Range( _
"I22:K22"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = _
"='Residential Family Survey'!R22C19:R22C21"
ActiveChart.SeriesCollection(1).Name = "='Residential Family Survey'!R22C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
"Residential Family Survey"
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=False, ShowSeriesName:=False, ShowCategoryName:= _
False, ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False
End Sub

 

[Spook E]

*Bump

Can anyone offer any advice?

Thanks