Range Object error
Nick
Thanks again I am starting to understand (sometimes Im slow but eventually I
get there)
So what you are saying is that in this statment
Worksheets("Data").Range(Cells(3, 10), Cells(6, 10)).Value = "World"
Excel is confused as to which CELLS I am refering. It can understand the
Worksheet and Range but it still believes the cells reference is pointing to
the place where the code originates?
So to solve my actual coding problem
Is there a syntax I can use to refetrence a multicell range on a worksheet
using numeric cell references rather than "A:1", "C:5" type references?
without constructing the WITH statment you have shown me previously?
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
|