Nick and Chad

I finally twigged to what Nick has been trying to tell me.

The syntax I was after is
Worksheets("Data").Range(Worksheets("Data").Cells( 3, 10), Worksheets
("Data").Cells(6, 10)).Value = "World"

Once I explicetly reference the CELLS all is well.
Thank you again
sorry for being so thick.
Lance




"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