Graph - Runtime Error 1004

[Ludo]

Hi,

I have following 'weird' problem.

If i run following code in DEBUG mode, there's no problem at all and
all works fine.
When i run it in REAL TIME (Run time), the code generates a Run-time
error 1004 at following line :
..SetSourceData Source:=ss.Range("A3:A" & lr & "," & I & "3:" & I &
lr), PlotBy:=xlColumns

Whatever i try (adding a wait time before the error generating line,
define ss as Variant) nothing works in Run time.
The only thing i have is an empty chart, no columns at all.

Anyone an idea whats happening and how to solve the problem?

Any help appreciated.
Ludo

I use :
Excel 2003 - SP3
Windows XP Professional Version 2002 - SP2

Sub Add_Chart(Datafilename As String)
'
Const FirstDataCellColumn = 7
Const DataOffset = 7
Const ChartRowIndex = 2
Dim MyOffset As Long
Dim ChartSheet As String
Dim ChartTitle As String
Dim ChartNumber As Integer
Dim LastRow As Long
Dim ChartLeftPositon As Integer
Dim ChartTopPosition As Integer
Dim ChartLeftPosition As Integer
Const ChartTitleFirstColumnPosition = 2
Dim ChartTitleColumn As Long
'
Application.ScreenUpdating = False

ChartLeftPosition = -164
ChartTopPosition = -122
Sheets(UnitFamilyName2).Select
DataSheet = ActiveSheet.Name
MyOffset = FirstDataCellColumn
Dummy2 = 1
Range("G3").Select
ChartTitleColumn = ChartTitleFirstColumnPosition
Set ss = Sheets(ActiveSheet.Name) 'Sheets("DU8X5 -
2010"), ...
lr = ss.Cells(Rows.Count, "B").End(xlUp).Row
lr = Weeknumber(Now()) + 3 ' set to 'from week 1 to actual
week'
Sheets.Add
ActiveSheet.Name = "Chart" & " " & UnitFamilyName2
ChartSheet = ActiveSheet.Name
For Each I In Array("g", "n", "u", "ab", "ai", "ap", "aw", "bd",
"bk", "br", "by", "cf", "cm", "ct", "da", "dh", "do", "dv", "ec",
"ej") 'col g & col n , ...
If Right(Sheets(UnitFamilyName2).Range(I & "3").Offset(-1,
-5).Value, 8) <> "Algemeen" Then
ChartTitle = Sheets(UnitFamilyName2).Range(I & "3").Offset(-1,
-5).Value
Charts.Add
With ActiveChart
.ChartType = xlColumnClusteredI & "3:" & I & lr), PlotBy:=xlColumns ' this line generates the run
time error 1004
.Location Where:=xlLocationAsObject, Name:=ChartSheet
End With
ChartTitleColumn = ChartTitleColumn + DataOffset
ChartNumber = ActiveSheet.Shapes.Count
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "FPY " & ChartTitle & "
HASS"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Week"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text =
"%FPY"
End With
Application.DisplayAlerts = True
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ActiveChart.ChartTitle.Select
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0.5
.MaximumScale = 1
End With
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 2
.TickMarkSpacing = 1
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
ActiveChart.ChartArea.Select
ChartLeftPosition = ChartLeftPosition + (Dummy2 * 20)
ChartTopPosition = ChartTopPosition + (Dummy2 * 20)
ActiveSheet.Shapes(ChartNumber).IncrementLeft
ChartLeftPosition
ActiveSheet.Shapes(ChartNumber).IncrementTop ChartTopPosition
ActiveWindow.Visible = False
Windows("Unit Data Logging.xls").Activate
Range("A1").Select
'prepare to create next chart
Dummy2 = Dummy2 + 1
MyOffset = MyOffset + DataOffset
Else
Dummy = True
End If
If Dummy = True Then Exit For
Next I
Windows("Unit Data Logging.xls").Activate
Range("A1").Select
Sheets("Data Logging").Select
End Sub

 

[Paul Robinson]

Hi
May be nothing (possibly set as module level variable already?), but
you don't have a
Dim ss as worksheet

regards
Paul

 

[Ludo]

Hi
May be nothing (possibly set as module level variable already?), but
you don't have a
Dim ss as worksheet

regards
Paul

Hi Paul,

Thanks for the tip.

Just implemented this line as you propose (Dim ss as worksheet) but i
still get en arror on the same line.
The error message now is : Methode 'Range' of object '_worksheet'
failed.

This means that i enter the VBA editor with the 'problem ' line
highlighted.
If i step now trough the code, all works fine, so whats happening?
As you can see in the original code, i make more than one chart (For
Each I in Array .... Next I)
So whenever i step trough the error line and go back to execute the
code in run time by click on the 'Run Sub / Userform' button in the
VBA editor, the second chart is generated withouth any problem at all,
so it seems to appear only when i generate the first chart.


Ludo

 

[Paul Robinson]

Hi
Does
Sheets(ActiveSheet.Name)

actually exist? Try
msgbox ActiveSheet.Name

to see the name of the active sheet before you set ss.
regards
Paul

 

[Ludo]

Hi
Does
Sheets(ActiveSheet.Name)

actually exist? Try
msgbox ActiveSheet.Name

to see the name of the active sheet before you set ss.
regards
Paul

Hi Paul,

Yes i have a valid worksheet name.

Debug.Print ActiveSheet.Name
Set Ss = Sheets(ActiveSheet.Name) 'Sheets("DU8X5 - 2010"), ...
Debug.Print Ss.Name

Both returns me DU8X5 - 2010

But i still get the error on line :
.SetSourceData Source:=Ss.Range("A3:A" & lr & "," & I &
"3:" & I & lr), PlotBy:=xlColumns

Perhaps i have to select first the data colums and then adding a chart
instead of adding a chart and afterward setting the chart data range?

i can give it a try, you never know.

If i execute following line into the Immediate window, it returns me
what i expect
?ss.Range("A3").Value >> Week
?ss.Range("A4").Value >> WK1
?ss.Range("A5").Value >> WK2

I guess there's a bug in the VBA compiler

The macro recorder isn't perfect too.
I got also a problem with pivot tables, but could solve this by moving
some code lines to an other place in the macro.
But to find this you are also more than a few hours later.


Regards,
Ludo

 

[Jon Peltier]

Is "I" supposed to be the column letter? Then you should put it in
quotes and treat it as a string. Excel thinks it's a variable, the way
you're using it, and if you have not declared it (I guess you don't
believe in Option Explicit), you never know what Excel will use as its
value. Maybe zero, maybe "".

Hint: use a variable for the address, so you can check it:

Dim sAddress as String
sAddress = "A3:A" & lr & "," & I & "3:" & I & lr
Debug.Print sAddress
..SetSourceData Source:=ss.Range(sAddress)

- Jon