Error 1004

[Sparky]

Hi All,
problem is driving me mad I get an almost random error in Excel
error 1004 when running some simple VBA I have 3 sheets in a Workbook
and I have writen some very simple code to move cell values around.
The statement that gives me the error is always of the form
Sheet1.Cells(Count1,3).Activate. it seems to be related to
the .Activate method and not the Objects. It also does not seem to be
caused by the same statement. I am running Excel 2007 part of Office
2007 and Windows 7 on a fairly modest spec PC

 

[Dave Peterson]

If sheet1 isn't the activesheet, then you'll get this error?

Is sheet1 active?

 

[Sparky]

If sheet1 isn't the activesheet, then you'll get this error?

Is sheet1 active?

Thanks for the quick reply
No Sheet1 is not active at all times and this is almost surly the
cause of the random nature of this error. Now how about the
supplementary question? If Sheet1 is not active and I want the make
Sheet1.Cells(Count1,3) active
do I have to use two statements something like Sheet1.Activate
and then Cells(Count1,3).Activate or is their a more elegant
way of so doing. I Dont mind the extra typing it just looks a bit
quick and dirty using two statements.

Sparky

 

[Dave Peterson]

First, you usually don't need to select or activate a cell to work with it.

If you were assigning a value, you could use:

sheet1.cells(...).value = "hi there"
and eschew the unnecessary steps of activation.

But if you want:

sheet1.parent.activate 'make sure the correct workbook is active
Sheet1.select 'or .activate
sheet1.cells(...).activate

=====
If you just wanted to select that single cell:

application.goto sheet1.cells(...), scroll:=true 'or false???

 

[Sparky]

First, you usually don't need to select or activate a cell to work with it.

If you were assigning a value, you could use:

sheet1.cells(...).value = "hi there"
and eschew the unnecessary steps of activation.

But if you want:

sheet1.parent.activate 'make sure the correct workbook is active
Sheet1.select 'or .activate
sheet1.cells(...).activate

=====
If you just wanted to select that single cell:

application.goto sheet1.cells(...), scroll:=true 'or false???

Once again thanks. I haven't tried the select method to make the sheet
active but I will. the reason I want to select the cell is to confirm
to the user that the correct cell has been selected by the software
prior to some other action / chain of events being instigated.

All the best
Sparky