To kind of add to what Nick was saying, think of the With statement
as a shortcut. It is primarily used to make reference when you dont
want to say something like :

Worksheet(1).Sheet("whatever").Range("somethingels e").someproperty = ...

this gets especially tedious when you want to keep adding statements that
only deal with the Range("somethingelse"). (Of course, YOU understand what
you're referencing, but as Nick was saying, Cells(3, 10) have a different
meaning
than .Cells(3, 10) . In the latter example, the "." means that you have a
relative reference somewhere, declared by a 'With'.

As an example, take the following set of statements:

With Worksheet("something").Range("A1:A6")
.Interior.ColorIndex = 39
.Font = Bold
.Value = 20
End With

the "." references are a shorthand notation for saying

Worksheet("something").Range("A1:A6").Interior.Col orIndex = 39
Worksheet("something").Range("A1:A6").Font = Bold
Worksheet("something").Range("A1:A6").Value = 20

see how that can get tedious ? ... I dont want to keep typing all that
predicate stuff, so I encase my statements in the With/End With block.

Hope this helps,
Chad





"NickHK" wrote:

Yes because which sheets does "Cells(3, 10)" refer to ?
Remember I said
<Quote
Notice ".Cells(3, 10)" is not necessarily the same as "Cells(3, 10)" , the
difference of a "."
</Quote

And this does NOT refer to a Range, but a Worksheet, so all object prefixed
with a "." refer to that worksheet :
With Worksheets("Data")
.Range(.Cells(3, 10), .Cells(6, 10)).Value = "World"
End With
So Range and Cells are on the WS "Data".

But this :
With Worksheets("Data")
.Range(Cells(3, 10), Cells(6, 10)).Value = "World"
End With
means the Range is on WS "Data", but Cells refers to sheet that contains the
code, which may or may not be WS "Data".

That is why is always better to give fully qualified ranges to be clear what
you mean. Unless the need the flexibility available with Range, Cells,
ActiveCell, ActiveSheet etc.

NickHK

"NoSoupForYou" wrote in message
...
Nick

Thanks for your patience
The Help on WITH tells me that it creates a temporary variable that
contains
a reference to the object in this case a RANGE object

I am expecting that ALL of the following statements are creating a
reference
to the RANGE object that contains the cells J3:J6 on the "Data" Worksheet
and
then assigning "World" to the VALUE property.
But the middle statment fails
Worksheets("Data").Range("J3:J6").Value = "World"
Worksheets("Data").Range(Cells(3, 10), Cells(6, 10)).Value = "World"
With Worksheets("Data")
.Range(.Cells(3, 10), .Cells(6, 10)).Value = "World"
End With


"NickHK" wrote:

Make sure you understand the meaning of the "With" block. Check the Help

What do you expect this to mean ?
Worksheets("Data").Range(.Cells(3, 10), .Cells(6, 10)).Value = "World"
Hint: It is not valid syntax, as it stands.

NickHK

"NoSoupForYou" wrote in message
...
Nick

Thanks for your quick reply and example that works where i had
previously
been met with naught but errors.

I guess I am still confused by the syntax highlighted by this example
Worksheets("Data").Range("J3:J6").Value = "Hello" 'This works fine
With Worksheets("Data") ' This works too as you have shown me
.Range(.Cells(3, 10), .Cells(6, 10)).Value = "World"
End With
Worksheets("Data").Range(.Cells(3, 10), .Cells(6, 10)).Value =
"World"
'but this fails
Worksheets("Data").Range(Cells(3, 10), Cells(6, 10)).Value = "World"
'
and this fails too

Do I always have to use the
With...
End With
Syntax just to get this to work
I thought that construction was simply shorthand for the single line
syntax
i have used that fails.
Is it because the WITH statment creates a new instance of the
WorkSheet
object?

"NickHK" wrote:

Lance,
When you get errors in these situations, it's normally a case of you
and
Excel not agreeing on exactly which Range/Cells are being
referenced.
Excel
always wins.
e.g. Which sheet do you think "Cells(1, 1)" refers to in your first
example
?
Debug.Print Cells.Parent.Name

I suspect what you really mean is :
With Worksheets("Data")
.Range(.Cells(3, 10), .Cells(6, 10)).Value = "World"
End With

Notice ".Cells(3, 10)" is not necessarily the same as "Cells(3, 10)"
,
the
difference of a "."

NickHK

"NoSoupForYou" wrote in
message
...
i All
This maybe a newby question but I have searched for an answer
without
any
luck.

When i attach the following code to a command button click event
Private Sub TestButton_Click()
Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True ' This
works
fine
Worksheets("Data").Range(Cells(1, 1), Cells(5, 3)).Font.Italic =
True
'
This produces a 1004 error

Worksheets("Data").Range("J3:J6").Value = "Hello" ' This works
fine
Worksheets("Data").Range(Cells(3, 10), Cells(6, 10)).Value =
"World"
'
This produces a 1004 error

Set myRange1 = Worksheets("Data").Range("J3:J6") 'This works
fine
Set myRange2 = Worksheets("Data").Range(Cells(3, 10), Cells(6,
10))
'
This produces a 1004 error
End Sub

I am obviously referencing the RANGE object incorrectly but I dont
see
how.

Is there anothe way people use to reference a multicell range
using
numeric
veriables??

Thanks for your assistance

Lance