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((RowValu e - 12) *
500, TradeAttribute)
Set EndInput = Sheets("Detail").Range("VarInput").Offset(495 + ((RowValue -
12) * 500), TradeAttribute)
Set BeginPL = Sheets("Detail").Range("VarInput").Offset(((RowVal ue - 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 Whe=xlLocationAsObject, Name:="Control"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "TBDRange"
End With

Sheets("Control").Select

End Sub

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

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.

"Sarah B via OfficeKB.com" wrote:

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

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.

--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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.

"Sarah B via OfficeKB.com" wrote:

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

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!

"STEVE BELL" wrote:

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.

--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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.

"Sarah B via OfficeKB.com" wrote:

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

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,
--
George Nicholson

Remove 'Junk' from return address.


"STEVE BELL" wrote in message
news:Ja1se.5900$yw4.1251@trnddc09...
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.

--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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.

"Sarah B via OfficeKB.com" wrote:

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

Michael,

Been there...

Appreciate your appreciation...

Will you be gone for the weekend????

--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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!

"STEVE BELL" wrote:

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.

--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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.

"Sarah B via OfficeKB.com" wrote:

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

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((RowValu e - 12) *
500, TradeAttribute)
Set EndInput = Sheets("Detail").Range("VarInput").Offset(495 + ((RowValue -
12) * 500), TradeAttribute)
Set BeginPL = Sheets("Detail").Range("VarInput").Offset(((RowVal ue - 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 Whe=xlLocationAsObject, Name:="Control"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "TBDRange"
End With

Sheets("Control").Select

End Sub

"George Nicholson" wrote:

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,
--
George Nicholson

Remove 'Junk' from return address.


"STEVE BELL" wrote in message
news:Ja1se.5900$yw4.1251@trnddc09...
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.

--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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.

"Sarah B via OfficeKB.com" wrote:

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

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))


--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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((RowValu e - 12)
*
500, TradeAttribute)
Set EndInput = Sheets("Detail").Range("VarInput").Offset(495 +
((RowValue -
12) * 500), TradeAttribute)
Set BeginPL = Sheets("Detail").Range("VarInput").Offset(((RowVal ue - 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 Whe=xlLocationAsObject, Name:="Control"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "TBDRange"
End With

Sheets("Control").Select

End Sub

"George Nicholson" wrote:

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,
--
George Nicholson

Remove 'Junk' from return address.


"STEVE BELL" wrote in message
news:Ja1se.5900$yw4.1251@trnddc09...
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.

--
steveB

Remove "AYN" from email to respond
"MichaelC" wrote in message
...
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.

"Sarah B via OfficeKB.com" wrote:

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

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 :

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))

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" wrote:


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 :

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))

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 :

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

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" wrote:

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" wrote:


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 :

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))

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" wrote:

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 :

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

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 :

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))

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

"keepITcool" wrote:



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 :

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))