Autofill method of range class failed - sometimes
 

HI all,

I am using Excel 97 (it has to work under it)

Here is the code I have that breaks
....

Code
-------------------
prev_points = 4 'this changes in a different place I just put it here so you can see it's value

Range("Calculations!G" & CStr(prev_points + 1)).AutoFill Destination:=Range("Calculations!G" & CStr(prev_points + 1) & ":Calculations!G" & CStr(21 + 1)), Type:=xlFillDefault

' Cell G5 has a formula that needs to be copied down.
-------------------

...

Well sometimes it doesn't break. Any ideas why

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187044591

Autofill method of range class failed - sometimes
 

Intellihome,

I would start by using the standard range object usage:

With Worksheets("Calculations")
.Range("G" & CStr(prev_points + 1)).AutoFill _
Destination:=.Range("G" & CStr(prev_points + 1) & ":G" & _
CStr(21 + 1)), Type:=xlFillDefault
End With

HTH,
Bernie
MS Excel MVP


"Intellihome" wrote in message
...

HI all,

I am using Excel 97 (it has to work under it)

Here is the code I have that breaks
...

Code:
--------------------
prev_points = 4 'this changes in a different place I just put it here
so you can see it's value

Range("Calculations!G" & CStr(prev_points + 1)).AutoFill
Destination:=Range("Calculations!G" & CStr(prev_points + 1) &
":Calculations!G" & CStr(21 + 1)), Type:=xlFillDefault

' Cell G5 has a formula that needs to be copied down.
--------------------

..

Well sometimes it doesn't break. Any ideas why?


--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870445918

Autofill method of range class failed - sometimes
 

Does it ever break when you run this test (with prev_points = 4)?

What does break mean?

Some more questions...

Is the worksheet protected?

How does this macro get started? If it's started by a commandbutton from the
control toolbox toolbar (or a different control from that toolbar), try changing
the .takefocusonclick property to false (under the properties menu).

Or add:
activecell.activate
at the top of your code.

(This is a bug in xl97 that was fixed in xl2k.)





Intellihome wrote:

HI all,

I am using Excel 97 (it has to work under it)

Here is the code I have that breaks
...

Code:
--------------------
prev_points = 4 'this changes in a different place I just put it here so you can see it's value

Range("Calculations!G" & CStr(prev_points + 1)).AutoFill Destination:=Range("Calculations!G" & CStr(prev_points + 1) & ":Calculations!G" & CStr(21 + 1)), Type:=xlFillDefault

' Cell G5 has a formula that needs to be copied down.
--------------------

..

Well sometimes it doesn't break. Any ideas why?

--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870445918

--

Dave Peterson

Autofill method of range class failed - sometimes
 

Hello Dave,

Well I figure that within an hour, there is a bug , but I could no
access this forum to post my reply.

And problem is in the FOCUS. It wasn't getting focus properly.

What I do is at the beginning of the code I take off focus of all th
other objects on the sheet and make this an active sheet. Then it work
fine.

Other wise I get subj error message. Weird ha?

Another thing I have problem with is when I am using scroll bar t
scroll a chart and on change event trying to update worksheet -- i
seems like it is skipping some of the function calls to update a sheet

What I mean is that sometimes it works sometimes it does not. So thi
is how I fixed it.

Sub ScrollBar1_Change()

Application.Calculate '<< I put this same function twice, and it work
all the time
Application.Calculate '<< second call to this function updates whol
worksheet

End sub

This works fine unless I have around 3000 lines on a sheet, then
really get problems updating twice that kind a sheet takes a long time


May be there is a better way to fix it, I am new to Excel, just coupl
of weeks since I've started this project.

The other problem I get is when I have that many lines on my sheet an
I put functions beyond column GE, they would not work. They work when
do not have that many lines or they work if I put them before tha
column.

For example

I would need to sum all the elements in column lets say HH so I put
function SUM(HH:HH) and I get error. If I put SUM(HH1:HH3000) It works
and it works if I do the same for other column or if I have less lines
about 200. Bugs bugs bugs... everywhere :

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187044591

Autofill method of range class failed - sometimes
 

Hello Dave,

Well I figure that within an hour, there is a bug , but I could no
access this forum to post my reply.

And problem is in the FOCUS. It wasn't getting focus properly.

What I do is at the beginning of the code I take off focus of all th
other objects on the sheet and make this an active sheet. Then it work
fine.

Other wise I get subj error message. Weird ha?

Another thing I have problem with is when I am using scroll bar t
scroll a chart and on change event trying to update worksheet -- i
seems like it is skipping some of the function calls to update a sheet

What I mean is that sometimes it works sometimes it does not. So thi
is how I fixed it.

Sub ScrollBar1_Change()

Application.Calculate '<< I put this same function twice, and it work
all the time
Application.Calculate '<< second call to this function updates whol
worksheet

End sub

This works fine unless I have around 3000 lines on a sheet, then
really get problems updating twice that kind a sheet takes a long time


May be there is a better way to fix it, I am new to Excel, just coupl
of weeks since I've started this project.

The other problem I get is when I have that many lines on my sheet an
I put functions beyond column GE, they would not work. They work when
do not have that many lines or they work if I put them before tha
column.

For example

I would need to sum all the elements in column lets say HH so I put
function SUM(HH:HH) and I get error. If I put SUM(HH1:HH3000) It works
and it works if I do the same for other column or if I have less lines
about 200. Bugs bugs bugs... everywhere :

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187044591

Autofill method of range class failed - sometimes
 

If this =SUM(HH:HH) returns an error, then I'm betting that there is an error in
column HH (maybe on a hidden row or after row 3000).

If you show all the rows, then select column HH, you can:
edit|goto special
click Constants
uncheck Numbers, Text, Logicals (leave Errors checked).

Then check the same thing in formulas:
select column HH
edit|goto special
click Formulas
uncheck Numbers, Text, Logicals (leave Errors checked).

If you get any cells selected, then clean up those errors and you'll be fine.

Intellihome wrote:

Hello Dave,

Well I figure that within an hour, there is a bug , but I could not
access this forum to post my reply.

And problem is in the FOCUS. It wasn't getting focus properly.

What I do is at the beginning of the code I take off focus of all the
other objects on the sheet and make this an active sheet. Then it works
fine.

Other wise I get subj error message. Weird ha?

Another thing I have problem with is when I am using scroll bar to
scroll a chart and on change event trying to update worksheet -- it
seems like it is skipping some of the function calls to update a sheet.

What I mean is that sometimes it works sometimes it does not. So this
is how I fixed it.

Sub ScrollBar1_Change()

Application.Calculate '<< I put this same function twice, and it works
all the time
Application.Calculate '<< second call to this function updates whole
worksheet

End sub

This works fine unless I have around 3000 lines on a sheet, then I
really get problems updating twice that kind a sheet takes a long time.

May be there is a better way to fix it, I am new to Excel, just couple
of weeks since I've started this project.

The other problem I get is when I have that many lines on my sheet and
I put functions beyond column GE, they would not work. They work when I
do not have that many lines or they work if I put them before that
column.

For example

I would need to sum all the elements in column lets say HH so I put a
function SUM(HH:HH) and I get error. If I put SUM(HH1:HH3000) It works,
and it works if I do the same for other column or if I have less lines,
about 200. Bugs bugs bugs... everywhere :)

--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870445918

--

Dave Peterson

Autofill method of range class failed - sometimes
 

Thanks Dave,

you were right, there were some errors in the formula after certai
line. In fact it was marked by another programmer, whoever did tha
before me.

Thanks again, you saved me a lot of time.
Ivan

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187044591

Autofill method of range class failed - sometimes
 

Thanks Dave,

you were right, there were some errors in the formula after certai
line. In fact it was marked by another programmer, whoever did tha
before me.

Thanks again, you saved me a lot of time.
Ivan

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187044591

Autofill method of range class failed - sometimes
 

Thanks Dave,

you were right, there were some errors in the formula after certai
line. In fact it was marked by another programmer, whoever did tha
before me.

Thanks again, you saved me a lot of time.
Ivan

--
Intellihom
-----------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=147
View this thread: http://www.msusenet.com/t-187044591

Autofill method of range class failed - sometimes
 

Glad you got it working.

Intellihome wrote:

Thanks Dave,

you were right, there were some errors in the formula after certain
line. In fact it was marked by another programmer, whoever did that
before me.

Thanks again, you saved me a lot of time.
Ivan.

--
Intellihome
------------------------------------------------------------------------
Intellihome's Profile: http://www.msusenet.com/member.php?userid=1479
View this thread: http://www.msusenet.com/t-1870445918

--

Dave Peterson