| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Object error
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
Chad
Thanks for your input. I think I understand the WITH statment but am confused by the fact that this statment ..... With Worksheets("Data") .Range(.Cells(3, 10), .Cells(6, 10)).Value = "World" End With cannot be replaced with this statment... Worksheets("Data").Range(.Cells(3, 10), .Cells(6, 10)).Value = "World" What I am after is a statment that refers to a multicell range using numeric cell references (so i can replace them with variables) rather than the usual "A:1","c:5" type reference. But without the extra complexity of the WITH construction Lance "ChadF" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Object error
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| peculiar error with Range() object | Excel Programming | |||
| Dyanamic range - Object Required Error? | Excel Programming | |||
| error 1004 Range object | Excel Programming | |||
| Dynamic range (object variable error) | Excel Programming | |||
| Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Linear Mode
