Ignore statement

Jagwarman
Jagwarman used Ask the Experts™
on
I am using the following piece of code which works fine when there is data to collect

strRange = Left(strRange, Len(strRange) - 1)
.Range(strRange).Copy

But it falls over with the statement 'Invalid procedure call or argument' when there is no data.

Could an expert out there provide me with the code that will move the code past this so that it does not fall over when there is no data

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
before the line that fails you can put 'on error resume next' if you know this is the only reason for the error.

its usually better to code around this sort of thing though by checking to see if the range is available. for example the following makes sure that strrange is at least 2 characters before trying to shorten it (your code should be trying to prove its a valid range as well)

If strRange > 2 then    
 strRange = Left(strRange, Len(strRange) - 1)
   .range(...
Endif
Top Expert 2008
Commented:
I agree with regmigrant on the approach, but he most likely intended his code to be the following (with the len() function):

If len(strRange) > 2 then    
 strRange = Left(strRange, Len(strRange) - 1)
   .range(...
Endif 

Open in new window


Thomas
d'oh, thanks for the catch

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial