Excel 2003 with VBA 6.3

Below is a short routine that is supposed to take data from an open
worksheet that has values in certin locations, and plot 3 data series
in each of 3 different charts.

The plotting works fine (have only gotten through the I = 1 condition)
except when I get down to the following line(Bold Font):



Code:
--------------------
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart " & I).IncrementLeft 1200
ActiveSheet.Shapes("Chart " & I).IncrementTop Top
Top = Top + 200
--------------------

I get the following message:
"The item with the specified name wasn't found"

The first time through the For...Next Loop the name should be "Chart
1", but somehow, I've discovered that the name becomes "Chart 2".

I don't know why it is changed. I save, then close the worksheet with
no other charts on it. I then reopen the worksheet. I even noticed that
when I stepped through the program that the first chart that is created
appears on a sheet named "Chart1"

Any clues?


Code:
--------------------
Option Explicit
Dim GetRow As Integer
Dim GetCol As Integer
Dim I As Integer
Dim J As Integer
Dim XAxisTitle As String
Dim YAxisTitle As String
Dim ChartTitle As String
Dim ReagentName As String
Dim MaterialType(3) As String
Dim Count As Integer
Dim FilterFileList
Dim MyStoredCalFile

Sub StoredCalPlots()

Count = 2 'Data contained on Sheet 2; Sheet 1 is where chart gets created

Range("A1").Select

XAxisTitle = "Analysis Date"
YAxisTitle = "Concentration Mean"
ReagentName = Range("C3").Value
Sheets(1).Name = ReagentName

GetRow = 7
GetCol = 5

Top = 0

For I = 1 To 3

MaterialType(I) = Range(Cells(GetRow, GetCol), Cells(GetRow, GetCol)).Value

Charts.Add
ActiveChart.SeriesCollection(1).Delete
ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

'Concentration Mean

ActiveChart.SeriesCollection(1).XValues = "= '" & Sheets(Count).Name & "'!" & "R8C4:R17C4" 'Analysis Date(always the same)
ActiveChart.SeriesCollection(1).Values = "= '" & Sheets(Count).Name & "'!" & "R8C" & GetCol & ":R17C" & GetCol 'Concentration Means for each material type
ActiveChart.SeriesCollection(1).Name = MaterialType(I)

'-2 SD Line

ActiveChart.SeriesCollection(2).XValues = "= '" & Sheets(Count).Name & "'!" & "R25C4:R26C4"
ActiveChart.SeriesCollection(2).Values = "= '" & Sheets(Count).Name & "'!" & "R25C5:R26C5"
ActiveChart.SeriesCollection(2).Name = "= '" & Sheets(Count).Name & "'!" & "R25C8"

'+2 SD Line

ActiveChart.SeriesCollection(3).XValues = "= '" & Sheets(Count).Name & "'!" & "R27C4:R28C4"
ActiveChart.SeriesCollection(3).Values = "= '" & Sheets(Count).Name & "'!" & "R27C5:R28C5"
ActiveChart.SeriesCollection(3).Name = "= '" & Sheets(Count).Name & "'!" & "R27C8"

ActiveChart.Location Whe=xlLocationAsObject, Name:=Sheets(Count).Name

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ReagentName & " " & MaterialType(I)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XAxisTitle
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = YAxisTitle
End With

ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 2
.Weight = xlHairline
.LineStyle = xlContinuous
End With

'Format Data Series -2 SD

ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlDot
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlSquare
.Smooth = True
.MarkerSize = 5
.Shadow = False
End With

'Format Data Series +2 SD

ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlDot
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlTriangle
.Smooth = True
.MarkerSize = 5
.Shadow = False
End With

ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart " & I).IncrementLeft 1200
ActiveSheet.Shapes("Chart " & I).IncrementTop Top
Top = Top + 200

ActiveSheet.Shapes("Chart " & I).ScaleWidth 1.3, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart " & I).ScaleHeight 1.3, msoFalse, msoScaleFromTopLeft
'ActiveChart.Legend.Select
'Selection.Delete

GetCol = GetCol + 1

Next I

Range("A1").Select
--------------------


--
scantor145
------------------------------------------------------------------------
scantor145's Profile: http://www.excelforum.com/member.php...o&userid=14766
View this thread: http://www.excelforum.com/showthread...hreadid=535500

Hi,

Try using the chartobjects name.

with ActiveSheet.Shapes(activechart.parent.name)
.IncrementLeft 1200
.IncrementTop Top
Top = Top + 200
.ScaleWidth 1.3, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.3, msoFalse, msoScaleFromTopLeft
end with

Cheers
Andy

scantor145 wrote:
Excel 2003 with VBA 6.3

Below is a short routine that is supposed to take data from an open
worksheet that has values in certin locations, and plot 3 data series
in each of 3 different charts.

The plotting works fine (have only gotten through the I = 1 condition)
except when I get down to the following line(Bold Font):



Code:
--------------------
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart " & I).IncrementLeft 1200
ActiveSheet.Shapes("Chart " & I).IncrementTop Top
Top = Top + 200
--------------------

I get the following message:
"The item with the specified name wasn't found"

The first time through the For...Next Loop the name should be "Chart
1", but somehow, I've discovered that the name becomes "Chart 2".

I don't know why it is changed. I save, then close the worksheet with
no other charts on it. I then reopen the worksheet. I even noticed that
when I stepped through the program that the first chart that is created
appears on a sheet named "Chart1"

Any clues?


Code:
--------------------
Option Explicit
Dim GetRow As Integer
Dim GetCol As Integer
Dim I As Integer
Dim J As Integer
Dim XAxisTitle As String
Dim YAxisTitle As String
Dim ChartTitle As String
Dim ReagentName As String
Dim MaterialType(3) As String
Dim Count As Integer
Dim FilterFileList
Dim MyStoredCalFile

Sub StoredCalPlots()

Count = 2 'Data contained on Sheet 2; Sheet 1 is where chart gets created

Range("A1").Select

XAxisTitle = "Analysis Date"
YAxisTitle = "Concentration Mean"
ReagentName = Range("C3").Value
Sheets(1).Name = ReagentName

GetRow = 7
GetCol = 5

Top = 0

For I = 1 To 3

MaterialType(I) = Range(Cells(GetRow, GetCol), Cells(GetRow, GetCol)).Value

Charts.Add
ActiveChart.SeriesCollection(1).Delete
ActiveChart.ChartType = xlXYScatterSmooth

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries

'Concentration Mean

ActiveChart.SeriesCollection(1).XValues = "= '" & Sheets(Count).Name & "'!" & "R8C4:R17C4" 'Analysis Date(always the same)
ActiveChart.SeriesCollection(1).Values = "= '" & Sheets(Count).Name & "'!" & "R8C" & GetCol & ":R17C" & GetCol 'Concentration Means for each material type
ActiveChart.SeriesCollection(1).Name = MaterialType(I)

'-2 SD Line

ActiveChart.SeriesCollection(2).XValues = "= '" & Sheets(Count).Name & "'!" & "R25C4:R26C4"
ActiveChart.SeriesCollection(2).Values = "= '" & Sheets(Count).Name & "'!" & "R25C5:R26C5"
ActiveChart.SeriesCollection(2).Name = "= '" & Sheets(Count).Name & "'!" & "R25C8"

'+2 SD Line

ActiveChart.SeriesCollection(3).XValues = "= '" & Sheets(Count).Name & "'!" & "R27C4:R28C4"
ActiveChart.SeriesCollection(3).Values = "= '" & Sheets(Count).Name & "'!" & "R27C5:R28C5"
ActiveChart.SeriesCollection(3).Name = "= '" & Sheets(Count).Name & "'!" & "R27C8"

ActiveChart.Location Whe=xlLocationAsObject, Name:=Sheets(Count).Name

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ReagentName & " " & MaterialType(I)
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = XAxisTitle
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = YAxisTitle
End With

ActiveChart.PlotArea.Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Interior.ColorIndex = xlNone
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 2
.Weight = xlHairline
.LineStyle = xlContinuous
End With

'Format Data Series -2 SD

ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlDot
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlSquare
.Smooth = True
.MarkerSize = 5
.Shadow = False
End With

'Format Data Series +2 SD

ActiveChart.SeriesCollection(3).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlThin
.LineStyle = xlDot
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlTriangle
.Smooth = True
.MarkerSize = 5
.Shadow = False
End With

ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart " & I).IncrementLeft 1200
ActiveSheet.Shapes("Chart " & I).IncrementTop Top
Top = Top + 200

ActiveSheet.Shapes("Chart " & I).ScaleWidth 1.3, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart " & I).ScaleHeight 1.3, msoFalse, msoScaleFromTopLeft
'ActiveChart.Legend.Select
'Selection.Delete

GetCol = GetCol + 1

Next I

Range("A1").Select
--------------------



--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info