Dyanamic range - Object Required Error?
 

I am trying to create an embedded chart with a named dynamic range so
that the range (and chart) will expand as new data is entered. The
code runs fine with a a normal range.

I created the "Named Range" on the Inventory worksheet. The range is
named "Vehicle_Count" with the following formula.

=OFFSET(Inventory!$D$55,0,0,COUNTA(Inventory!$D:$D ),2)

The data for the range resides on the Inventory worksheet . C55 though
C85 are the different names of the inventory (cars, trucks, buses. etc.)
D55 through D85 holds the number of cars, trucks, buses, etc. in
inventory.

The code below stops at .SetSourceData Source:=Vehicle_Count with an
Object Required Error? The code is below.

Al you ideas would be appreciated. Named ranges, well heck just
ranges, confuse me.

Thanks

Craig

--------------------
Sub AddEmbeddedChart(sToSheet As String, sTitle As String, sRng As
Range, sUpper As String, sLeft As String, sWidth As String, sHeight As
String, sName As String)
Dim chrtNew As Chart
Dim rngVehicles As Range

Sheets(sToSheet).Select

Set chrtNew = Charts.Add
Set chrtNew = chrtNew.Location(whe=xlLocationAsObject,
Name:=sToSheet) 'Sheet where chart will be placed
With chrtNew
..ChartType = xlColumnClustered

..SetSourceData Source:=Vehicle_Count 'Dyanmic range not
working

'Object Required error
..HasTitle = True
..ChartTitle.Text = sTitle
..Legend.Position = xlLegendPositionBottom
..HasLegend = False
..HasDataTable = False
..HasLegend = False
With .Parent
..Top = Range(sUpper).Top
..Left = Range(sLeft).Left
..Width = sWidth
..Height = sHeight
..RoundedCorners = True
..Shadow = True
..Visible = True
..Name = sName 'Name that gets activated for chart
manipulation
End With
End With

ActiveChart.Deselect 'Removes all handles
End Sub


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=530537