Button programming Run-time error '1004' with range
 

Hi,

I've done a small program simulating a MonteCarlo sampling and I link
button to generate the chart.

I got this problem and I don't understant why...

Run-time error '1004':

Method 'range' of object '_Worksheet' failed

As you will see the code is very simple...


Code
-------------------

Private Sub btnGenerateChart_Click()
Dim iRepeatMonteCarlo As Integer
Dim iNbSeries As Integer

Dim rgSource As Range

iRepeatMonteCarlo = Range("'SimulationMonteCarlo'!D6").Value
iNbSeries = Range("'SimulationMonteCarlo'!D7").Value

rgSource = Range("'Stat Data'!B7:B96") ' Error 1004 here

'Set rgSource = Range("'Stat Data'!B7:B96") 'Error 1004 here

Call GenerateChart(rgSource, iRepeatMonteCarlo, iNbSeries)
End Sub

-------------------


The range is right because I use the same on the Chart_Activate event.

Thank you

Nic

--
El_Pabl
-----------------------------------------------------------------------
El_Pablo's Profile: http://www.excelforum.com/member.php...fo&userid=3312
View this thread: http://www.excelforum.com/showthread.php?threadid=52965

Button programming Run-time error '1004' with range
 

You need the set statement as a range is an object (as opposed to a
variable). Additionally you need to reference the sheet and then the range
something lkie this...

set rgSource = sheets("Stat Data").Range("B7:B96")
--
HTH...

Jim Thomlinson


"El_Pablo" wrote:


Hi,

I've done a small program simulating a MonteCarlo sampling and I link a
button to generate the chart.

I got this problem and I don't understant why...

Run-time error '1004':

Method 'range' of object '_Worksheet' failed

As you will see the code is very simple...


Code:
--------------------

Private Sub btnGenerateChart_Click()
Dim iRepeatMonteCarlo As Integer
Dim iNbSeries As Integer

Dim rgSource As Range

iRepeatMonteCarlo = Range("'SimulationMonteCarlo'!D6").Value
iNbSeries = Range("'SimulationMonteCarlo'!D7").Value

rgSource = Range("'Stat Data'!B7:B96") ' Error 1004 here

'Set rgSource = Range("'Stat Data'!B7:B96") 'Error 1004 here

Call GenerateChart(rgSource, iRepeatMonteCarlo, iNbSeries)
End Sub

--------------------


The range is right because I use the same on the Chart_Activate event.

Thank you

Nick


--
El_Pablo
------------------------------------------------------------------------
El_Pablo's Profile: http://www.excelforum.com/member.php...o&userid=33129
View this thread: http://www.excelforum.com/showthread...hreadid=529652

Button programming Run-time error '1004' with range
 

Try this variation:
set rgSource = worksheets("stat data").Range("B7:B96")

When code is behind a worksheet module, then unqualified ranges belong to that
sheet that holds the code.

So you're really doing:

set rgSource = me.range("'stat data'!b7:b96")

And there isn't a range like that.



El_Pablo wrote:

Hi,

I've done a small program simulating a MonteCarlo sampling and I link a
button to generate the chart.

I got this problem and I don't understant why...

Run-time error '1004':

Method 'range' of object '_Worksheet' failed

As you will see the code is very simple...

Code:
--------------------

Private Sub btnGenerateChart_Click()
Dim iRepeatMonteCarlo As Integer
Dim iNbSeries As Integer

Dim rgSource As Range

iRepeatMonteCarlo = Range("'SimulationMonteCarlo'!D6").Value
iNbSeries = Range("'SimulationMonteCarlo'!D7").Value

rgSource = Range("'Stat Data'!B7:B96") ' Error 1004 here

'Set rgSource = Range("'Stat Data'!B7:B96") 'Error 1004 here

Call GenerateChart(rgSource, iRepeatMonteCarlo, iNbSeries)
End Sub

--------------------

The range is right because I use the same on the Chart_Activate event.

Thank you

Nick

--
El_Pablo
------------------------------------------------------------------------
El_Pablo's Profile: http://www.excelforum.com/member.php...o&userid=33129
View this thread: http://www.excelforum.com/showthread...hreadid=529652

--

Dave Peterson