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