Debug RuntimeError 1004 in Chart

[MichaelC]

I'd truly appreciate any help in debugging the RunTimeError1004 that crops up
on most of the coding lines in the macro below.
Thanks in advance for your kind help.

Sub ChartInteger()
'This Chart is drawn to show the outcome of inputting 400+ different values
into an active cell in Sheet("Control")
'The active cell can be any one of a 10x12 range where the column headings
are called TradeAttribute
'A separate macro iterates the 400 input values into each cell and writes
them, and the result of applying
'a complex formula, onto another Sheet("Detail")
'The Sheet("Detail") contains 2x400 row columns of outcomes from varying the
values of each Active Cell 400 times
'I want to use the Active Cell is the identifier for each of the 400 row
columns of Chart data in Sheet("Detail")
'RunTime error 1004 appears at virtually every line

RowValue = ActiveCell.Row
ColumnValue = ActiveCell.Column
TradeAttribute = Sheets("Control").Cells(11, ColumnValue)

ActiveSheet.ChartObjects("ChartInteger").Select
Set BeginInput = Sheets("Detail").Range("VarInput").Offset((RowValue - 12) *
500, TradeAttribute)
Set EndInput = Sheets("Detail").Range("VarInput").Offset(495 + ((RowValue -
12) * 500), TradeAttribute)
Set BeginPL = Sheets("Detail").Range("VarInput").Offset(((RowValue - 12) *
500), TradeAttribute + 12)
Set EndPL = Sheets("Detail").Range("VarInput").Offset((495 + (RowValue - 12)
* 500), TradeAttribute + 12)


Set InPutData = Sheets("Detail").Range(Cells(BeginInput, 0), Cells(EndInput,
0))
Set PLData = Sheets("Detail").Range(Cells(BeginPL, 0), Cells(EndPL, 0))

ActiveChart.SeriesCollection(1).Values = "=Detail!InputData"
ActiveChart.SeriesCollection(1).XValues = "=Detail!PLData"
ActiveChart.SeriesCollection(1).Name = "=Detail!C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Control"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "TBDRange"
End With

Sheets("Control").Select

End Sub

 

[Sarah B via OfficeKB.com]

Michael,

What do you mean when you say that it crops up on most of the line? I'm
assuming you've done something like putting breakpoints on lines, commenting
out other lines, etc?

Sarah

 

[MichaelC]

The runtime error appeared the first time I ran the Sub, and when I hit
Debug, it highlighted, in yellow, the offending line of code.
I fixed what I thought was wrong and ran the Sub again.
The runtime error reappeared but highlighting a different line of code.
This has gone on all morning - with sometimes different, sometimes the same
line of code.
So I can't even pinpoint exactly which line is in error.
Perhaps the error is apparent to an experienced eye such as yours.

 

[STEVE BELL]

Make sure that
Option Explicit
is at the top of your module.

Use the Debug menu
select - Compile Project
(you can also add a toolbar button to do this)

Each time you compile Excel will high light an offending line of code.
Try to fix the line.
Continue this until the code is clean.

 

[MichaelC]

Thanks, Steve - for teaching me how to fish rather than giving me one.
Although I've heard some ladies say that if you teach a man how to fish
rather than giving him one, you get rid of him for the rest of the weekend!

 

[George Nicholson]

Since your code starts with:
RowValue = ActiveCell.Row
ColumnValue = ActiveCell.Column
it might also be important to make sure you start with the same sheet (&
cell) active while doing initial debugging, especially until you eliminate
the "inconsistent" results.

HTH,

 

[STEVE BELL]

Michael,

Been there...

Appreciate your appreciation...

Will you be gone for the weekend????

 

[MichaelC]

Thanks Steve and George. I have Compiled the Project until the menu option
to do so is no longer available. And I still get the RunTime Error 1004 at
the code line indicated below (Yes, I know there are probably more elegant
ways of "Dim"ming things, and would apreciate a pointer on that too)

Sub ChartInteger()
'This Chart is drawn to show the outcome of inputting 400+ different values
into an active cell in Sheet("Control")
'The active cell can be any one of a 10x12 range where the column headings
are called TradeAttribute
'A separate macro iterates the 400 input values into each cell and writes
them, and the result of applying
'a complex formula, onto another Sheet("Detail")
'The Sheet("Detail") contains 2x400 row columns of outcomes from varying the
values of each Active Cell 400 times
'I want to use the Active Cell is the identifier for each of the 400 row
columns of Chart data in Sheet("Detail")
'RunTime error 1004 still appears even though I have complied the project

Dim Dn As Integer
Dim Across As Integer
Dim i As Integer
Dim Cell As Variant
Dim StartVal As Single
Dim NumToFill As Single
Dim TradeAttribute As Integer
Dim Cnt As Integer
Dim MaxVal As Variant
Dim RowValue As Integer
Dim ColumnValue As Integer
Dim BeginInput As Range
Dim EndInput As Range
Dim BeginPL As Range
Dim EndPL As Range
Dim InputData As Range
Dim PLData As Range


RowValue = ActiveCell.Row
ColumnValue = ActiveCell.Column
TradeAttribute = Sheets("Control").Cells(11, ColumnValue)


Set BeginInput = Sheets("Detail").Range("VarInput").Offset((RowValue - 12) *
500, TradeAttribute)
Set EndInput = Sheets("Detail").Range("VarInput").Offset(495 + ((RowValue -
12) * 500), TradeAttribute)
Set BeginPL = Sheets("Detail").Range("VarInput").Offset(((RowValue - 12) *
500), TradeAttribute + 12)
Set EndPL = Sheets("Detail").Range("VarInput").Offset((495 + (RowValue - 12)
* 500), TradeAttribute + 12)

'This is where the RunTime Error 1004 now appears

Set InputData = Sheets("Detail").Range(Cells(BeginInput, 0), Cells(EndInput,
0))
Set PLData = Sheets("Detail").Range(Cells(BeginPL, 0), Cells(EndPL, 0))
ActiveSheet.ChartObjects("ChartInteger").Select
ActiveChart.SeriesCollection(1).Values = "=Detail!InputData"
ActiveChart.SeriesCollection(1).XValues = "=Detail!PLData"
ActiveChart.SeriesCollection(1).Name = "=Detail!C1"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Control"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "TBDRange"
End With

Sheets("Control").Select

End Sub

 

[STEVE BELL]

You're very welcome!

Now:

Dim your row variables as Long (not as Integer). (It will fail when it gets
to about 32,000)

Be careful with Single and Variant. Using Variant makes your code work
harder.
Double check the help file on variable types. (select Long, or Single, or
..... and use F1) Than search for the list of all variable types.


When using Range(Cells(row, col)) in your code
row and col must be greater than 0

Set InputData = Sheets("Detail").Range(Cells(BeginInput, 0), Cells(EndInput,
0))

 

[keepITcool]

also note that OP uses RowValue for offset.
AND multiplies the value by 500

(495 + (RowValue - 12) * 500

495*500 = 247500...
which is a little larger than the number of available rows.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


STEVE BELL wrote :

 

[MichaelC]

Thank you Steve and Meneer/Mevrouw Nederlander from the country my parents
came from.
I shall try both your suggestions in the morning PST and hope this will
slay the chart-dragon.
Does OP mean Operating Procedure? The formula problem ITCool refers to is
OK because of the difficult to see double parentheses - but thanks for
checking.
Michael

 

[keepITcool]

OP stands for original poster.
it's meneer name of Jurgen

Formula may be ok BUT you must check the value
of rowvalue BEFORE attempting the offset
else you get a runtime 1004


If original range is on row 1
valid values for rowvalue are 12 thru 142 only,
giving offset from 495 thru 65495


Set EndPL = Sheets("Detail").Range("VarInput") _
.Offset((495 + (RowValue - 12) * 500), TradeAttribute + 12)

(495 + (RowValue - 12) * 500)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


MichaelC wrote :

 

[MichaelC]

Steve: I have changed the code to ensure there are no "0" in the Cells
expression, but still get the same runtime error 1004.
it now reads:
Set InputData = Sheets("Detail").Range(Cells(BeginInput, 1), Cells(EndInput,
1))
Michael

 

[MichaelC]

Wel bedankt, Jurgen - I have added an "If Then" statement to exit the Sub if
RowValue < 12.
But I still get a runtime error 1004 at the statement:

Set InputData = Sheets("Detail").Range(Cells(BeginInput, 1), Cells(EndInput,
1))

Debug/Compile VBA Project runs clean.
I don't get it. My inherited stubborness is being tested.

 

[keepITcool]

the cells are NOT qualified THUS refer to activesheet
the Range is qualified thus refers to Details..
better to reverse or qualify all..

With worksheets("Detail")
set inputdata = .Range( _
.Cells(BeginInput,1),
.Cells(EndInput,1))
end with



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


MichaelC wrote :

 

[MichaelC]

Steve, George and Jurgen - I finally got it to work. Thanks for staying with
me all this time. For the benefit of those other novices who get floored by
the mysteries of coding, here is the code that finally worked for me:

RowValue = ActiveCell.Row
ColumnValue = ActiveCell.Column
TradeAttribute = Sheets("Control").Cells(11, ColumnValue)
If RowValue < 12 Then Exit Sub

With Worksheets("Detail")
Set BeginInput = Range("VarInput").Offset((RowValue - 12) * 500,
TradeAttribute)
Set EndInput = Range("VarInput").Offset(495 + ((RowValue - 12) * 500),
TradeAttribute)
Set BeginPL = Range("VarInput").Offset(((RowValue - 12) * 500),
TradeAttribute + 12)
Set EndPL = Range("VarInput").Offset((495 + (RowValue - 12) * 500),
TradeAttribute + 12)
Set InputData = Range(BeginInput, EndInput)
Set PLData = Range(BeginPL, EndPL)
End With
ActiveSheet.ChartObjects("ChartInteger").Select