Works at one place but not another?

[OrientalPearl]

Hello everyone,

Basically, a sub-routine 'ResetImportSection' is called twice in the
VBA code. However it works well for the first one but fires a 'runtime
error 1004: Select method of Range class failed.' at the second place.
ResetImportSection is used to tidy up in case of an import failure. It
is called if the import file is corrupted somehow in the 1st instance
and it works fine. The other piece of code also calls it when too many
days are involved(at most 7 days). That's where it crashs over the
following stagement within ResetImportSection:

'Select the range to be cleared
masterworkbook.Sheets("Shipper").Range("G8:G12").Select

But it continues to run(since I can see the pane is cleared as desired)
if I click the 'continue' button afterwards.

Can why does it crash when it is invoked in the secone case? Thanks go
to any posts.

Regards
Frank

 

[Tom Ogilvy]

If masterworkbook.Sheets("Shipper") isn't the activesheet, then you would
have that problem.

 

[Guest]

I don't know exactly why, but I think you have to break the statement up.
(It's just a guess, but maybe something to do with a workbook object not
having a select method precludes you from including it in the statement??)

MasterWorkBook.Activate
Sheets("Shipper").Range("G8:G12").Select

Or, you could try to clear the range w/o selecting it.
masterworkbook.Sheets("Shipper").Range("G8:G12").Clear

 

[OrientalPearl]

Thanks Tom for your reply. Sorry for not mentioning that the whole
workbook has been set active right before the selection statement.

'Set up masterworkbook
Dim masterworkbook As Workbook
Set masterworkbook = ActiveWorkbook

'Select the range to be cleared
masterworkbook.Sheets("Shipper").Range("G8:G12").Select

It's worthwhile noting that the same routine does work in the first
scenario(when the import file is corrupted; whenever a bad file format
is imported, it is invoked and executed without error), but not in the
second one. That's why I feel so perplexed. =S

 

[Tom Ogilvy]

What you have offered has nothing to do with what I said. Changing the
activesheet is as easy as doing

Worksheets("Dog").Activate

You can not select except on the activesheet. If you are opening a file, it
is highly probably (without seeing your code- but reinforced by the fact
that you are even attempting to select that range) that Shipper is not the
activesheet, no matter how many references you might have set to workbook
that contains it.

 

[Tom Ogilvy]

works fine for me as long as the sheet is active: (as demo'd from the
immediate window)

set masterworkbook = Activeworkbook
masterworkbook.Sheets("Shipper").Range("G8:G12").select
? selection.Address(external:=True)
[Book1]Shipper!$G$8:$G$12


If Shipper isn't the active sheet, then of course it will not work. If
activating MasterWorkbook makes Shipper that activesheet, then it would work
by breaking it up. If not then you would need to break it up againg

Masterworkbook.Activate
Worksheets("Shipper").Activate
Range("G8:G12").Select

or

Application.Goto masterworkbook.Sheets("Shipper").Range("G8:G12")

 

[OrientalPearl]

Thanks JMB for your post. The workbook has originally been set as
active. Your second solution works, i.e. Act without prior selection,
requiring modification to all selection statements. I found it solves
the problem by setting the specific worksheet, not workbook, active,
i.e. adding the following command prior to the group of selection
statements(Sorry, it clears up other relevant ranges as well besides
the one provided in my 1st post):

masterworkbook.Sheets("Shipper").Activate

Not sure why explicit activition of the worksheet is required since
every selection statement started with
'masterworkbook.Sheets("Shipper").Range...'. Still not sure why the
sub-routine behaved differently when being invoked.

Thanks to you both and regards
Frank

 

[OrientalPearl]

You're purely correct, Tom. Explicit activition of the worksheet is the
key. Though (quoted from my another post):

'Not sure why explicit activition of the worksheet is required since
every selection statement started with
'masterworkbook.Sheets("Shipper").Range...'. Still not sure why the
same
sub-routine behaved differently when being invoked. '


Thanks and regards
Frank

 

[Tom Ogilvy]

Sorry to say, but
If they said that that just reinforces that you were talking to the wrong
person.

 

[Guest]

You are correct. When I was playing with it, my target worksheet must have
been the activesheet when I activated the workbook (as you probably
suspected).

works fine for me as long as the sheet is active: (as demo'd from the
immediate window)

set masterworkbook = Activeworkbook
masterworkbook.Sheets("Shipper").Range("G8:G12").select
? selection.Address(external:=True)
[Book1]Shipper!$G$8:$G$12


If Shipper isn't the active sheet, then of course it will not work. If
activating MasterWorkbook makes Shipper that activesheet, then it would work
by breaking it up. If not then you would need to break it up againg

Masterworkbook.Activate
Worksheets("Shipper").Activate
Range("G8:G12").Select

or

Application.Goto masterworkbook.Sheets("Shipper").Range("G8:G12")

--
Regards,
Tom Ogilvy

I don't know exactly why, but I think you have to break the statement up.
(It's just a guess, but maybe something to do with a workbook object not
having a select method precludes you from including it in the statement??)

MasterWorkBook.Activate
Sheets("Shipper").Range("G8:G12").Select

Or, you could try to clear the range w/o selecting it.
masterworkbook.Sheets("Shipper").Range("G8:G12").Clear