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 |
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 |