Cells vs Range?

[danomaniac]

I inherited a newer pc than I had been using. My old one was running NT 4.0 sp6 and the new is Win2k sp1. I am writing an Excel app and can't use the 'cells(x,y)' reference in VBA. I have to use 'Range("A1&quot' I always used the 'Cells' method before so why can't I on the new machine? I get an 'Application-defined or object-defined error'

Thanks for any help

Dan

"It's more like it is now, than it ever has been."

 

[xlbo]

there should be no reason you can't use the Cells syntax - I do so all the time (XP)

can you post the code that is causing the error ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[danomaniac]

Here is the code I'm using. I am trying to clear the contents of a sheet that is pre-formatted, then copy the data from a query run on another sheet.


Sub buildDatasheet()
DataSheet.Activate

'This line causes the error
'during execution but works
'fine in the immediate window
Range(Cells(2, 1), Cells(65536, 8)).Select

Selection.ClearContents

QuerySheet.Activate

'get lastrow
Cells(65536, 1).Select
Selection.End(xlUp).Select
lastrow = Selection.Row

QuerySheet.Range(Cells(2, 2), Cells(lastrow, 8)).Copy
DataSheet.Cells(2, 1).PasteSpecial (xlValues)

End Sub


"It's more like it is now, than it ever has been."

 

[xlbo]

Is this run from a button ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[danomaniac]

Not yet, it's just run from the vba window.

I got it working, I copied the code into a new module and it worked.

Don't really know why that should make a difference. I know I've done this before from a worksheet and it's worked so I don't know what's different now.

You don't need to worry about it if you don't want to, I'm moving on.

Thanks,

Dan

"It's more like it is now, than it ever has been."

 

[xlbo]

fair enough - I was going to suggest setting the button's takefocusonclick property to false if you were running it from a button. Glad it's sorted

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244