Re: problem referring to Range and Cells

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Phillips (bob.phillips_at_notheretiscali.co.uk)
Date: 02/28/04 

Date: Sat, 28 Feb 2004 01:05:50 -0000

That is correct, but an object can have a property, which is an object in
itself,. so you can have nested Withs , all referring to the top-level
object. This is what I used to circumvent the problem you had encountered
with

    With Worksheets("Sheet2")
        With .Range(.Cells(3, 1), .Cells(3, 5)) 

-- 
HTH
Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Kevin" <anonymous@discussions.microsoft.com> wrote in message
news:9809FD37-4DB2-4689-9BB6-E7350E43F1CC@microsoft.com...
> Thanks Bob and Tom.  This is good to know.  That is, when using the With
statement,
> qualify Worksheets and Ranges separately.
>
> I just now found the following in VB User Interface Help.
>
>      Note   Once a With block is entered, [the specific] object can't be
changed.
>      As a result, you can't use a single With statement to affect a number
of
>      different objects.
>
> Thanks again.
>
> Kevin

Relevant Pages

  • Re: workbook opening
    ... "Bob Phillips" wrote in message ... > Hi Kevin, ... > Function IsWbOpen(FileName As String) As Boolean ...
    (microsoft.public.excel.misc)
  • Re: Referencing a Value in variable length columns.
    ... Here is a data sample (I adjusted the formula accordingly as my data is in ... I used the same set up as described above to retrieve the MIN date and time, ... Kevin ... "Bob Phillips" wrote: ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Referencing a Value in variable length columns.
    ... >> Kevin, ... >> Bob Phillips ... >> (remove nothere from the email address if mailing direct) ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Villein
    ... Kevin, let's reel this back a bit here. ... There was nothing purposefully vague or misleading about my ... I apologize for referring to your activity as "bat- ...
    (rec.games.trading-cards.jyhad)
  • Re: Conditional Formating - More then 3 conditioned with formula
    ... What code are you referring to? ... Bob Phillips ... (replace somewhere in email address with gmail if mailing direct) ...
    (microsoft.public.excel.worksheet.functions)