excel97: runtime error 1004 select method of range class failed

[JMCN]

hi

i receive an error message whenever i try to step through the
following code. i don't know why excel cannot find the cells.select ?
if anyone has an suggestions as to why, it would be greatly
appreciated.

thanks in advance - jung

i only posted a couple of lines below to get the just of the problem.
++++++++++++++++++++++++++++++
Dim lastrow As Integer

If Workbooks.Count <> 2 Then
Beep
MsgBox "FUNCTION CANCELLED! " + Chr$(13) + Chr$(13) & "The
Macro workbook and the workbook to be formatted should be the only
workbooks open.", vbCritical
Exit Sub
End If


ActiveWindow.ActivateNext
lastrow = Range("A65536").End(xlUp).Row ' determine last row
of data
ActiveWindow.Zoom = 85
Cells.Font.Name = "Arial"
Cells.Select 'recevie runtime error 1004
Selection.Columns.AutoFit

Range("A1").Select
ActiveCell.FormulaR1C1 = "INTERNAL #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "OBLIGOR #"

 

[Colo]

Hi JMCN,

Sorry now I don't have 97....It may be a problem comes from forcus.
Insert "ActiveCell.Activate" before "Cells.Select" line.


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

ActiveCell.Activate
Cells.Select 'recevie runtime error 1004
Selection.Columns.AutoFit

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


Or, simply don't select cells


Code

 

[Dave Peterson]

How are you executing the code?

From a commandbutton from the controls toolbar placed on the worksheet?

If yes, try changing its .takefocusonclick property to false. (If it's a
control from that toolbar that doesn't have that property, try adding:

activecell.activate

to the top of your routine (a bug fixed in xl2k).

 

[Tom Ogilvy]

Just an alternate opinion (assuming this code is in a sheet module).
If you are running this from the click event of a commandbutton, then your
problem won't be fixed with activeCell.Activate or Changing TakeFocusOnClick
to false I don't think (although those should be done in xl97 as well).
Since you have ActiveWindow.ActivateNext this would indicate the
activesheet is no longer the worksheet containing the code. Thus the
unqualified Cells.Select refers to the worksheet containing the code and not
the activeworksheet as it would in a general module. The easiest solution
would be to move your code to a general module, then call it from the click
event. The harder solution is to qualify all your references so you state
specifically where the action should take place

Activesheet.Cells.Select vice Cells.select for instance.

unqualified references in a sheet module have an implicit "me" qualifier
me.cells.select

 

[JMCN]

Just an alternate opinion (assuming this code is in a sheet module).
If you are running this from the click event of a commandbutton, then your
problem won't be fixed with activeCell.Activate or Changing TakeFocusOnClick
to false I don't think (although those should be done in xl97 as well).
Since you have ActiveWindow.ActivateNext this would indicate the
activesheet is no longer the worksheet containing the code. Thus the
unqualified Cells.Select refers to the worksheet containing the code and not
the activeworksheet as it would in a general module. The easiest solution
would be to move your code to a general module, then call it from the click
event. The harder solution is to qualify all your references so you state
specifically where the action should take place

Activesheet.Cells.Select vice Cells.select for instance.

unqualified references in a sheet module have an implicit "me" qualifier
me.cells.select

thank you all for your advice. i forgot to include that i run the
code by clicking the commandbutton1.

thanks again!!!