I have a question about set range. There is something that I am trying and it's not working. Let's say I have range r, for example I wrote:
set r = range("a1")
But this range is valid for the active worksheet, for example if I am on worksheet1 and I write r.value = 1 then it will change the value of that cell on the active worksheet. Now I want to change the value of that specific range on another worksheet. So I write
worksheets("specificworksheet").r.value =1
But when I write this, it says Object doesn't support this propert or method. why is that? How can i assign a range (that was already set) for another worksheet? In this case writing
worksheets("specificworksheet").range("a1").value=1
would solve my problem, however, somethimes I have more complicated range, for example if I assinged a cell to r, where I used cells.find(...). Appreciate any help, thanks!
Click on Insert tab > select Module. Step 2: Write the subprocedure for VBA Set Range as shown below. Step 3: Declare the variable using DIM as Range object as shown below. Step 4: Further setting up the range object with declared variable MyRange, we will then choose the cell which wants to include.
To set the reference, we need to use the “Set” keyword. Now the variable “Rng” refers to the range of cells from A2 to B10. Instead of writing “Range(“A2:B10”))” every time, we can simply write the word “Rng.” In the next line, mention the variable name “Rng” and put a dot to see the magic.
You could use Address:
worksheets("specificworksheet").range(r.Address).value=1
When you do:
Set r = Range("A1")
What you're really doing is this:
Dim r As Variant
Set r = Application.ActiveSheet.Range("A1")
So declare all your variables, and always specify Option Explicit
at the top of every module - that way VBA will refuse to compile code that uses undeclared variables (or typos). And declare your variables with an explicit type:
Dim r As Range
A Range
object knows about its Parent
, which is the worksheet it belongs to; that's why, as you noted, this range is valid for the active worksheet. And it remains on that sheet even if you activate another sheet (which you wouldn't need to do 99.999% of the time anyway).
That's why you can't do this:
Worksheets("Sheet42").r.Value = 1
Because r
isn't a member of the Worksheet
object - it's a local object variable that points to a very specific address on a very specific worksheet. Now, when you do Worksheets("Sheet42")
, you're really accessing the default property of the Worksheets
collection class, which is its Item
property:
Dim sheet As Worksheet
Set sheet = Worksheets.Item("Sheet42")
And the Worksheets
collection's Item
property returns an Object
, which means any member call you add after that is going to be late-bound / resolved at run-time:
Dim obj As Object
Set obj = Worksheets.Item("Sheet42")
obj.AnythingYouWantHereWillCompileAnyway
At run-time, VBA queries the object's interface to look for AnythingYouWantHereWillCompileAnyway
, and doesn't find that member - and that's how you get a run-time error 438 - "object doesn't support this property or method".
You can move that type-safety back to compile-time (instead of run-time) by working with early-bound calls, i.e. work with the Worksheet
interface/class instead of Object
:
Dim obj As Worksheet
Set obj = Worksheets.Item("Sheet42")
obj.AnythingYouWantHereWillCompileAnyway 'nope. that won't compile anymore.
When you ask how can I assign a range (that was already set) for another worksheet?, you assume a Range
object is nothing more than an address - and that assumption is wrong.
A Range
is much more than just an address. If it were just an address, then it would be a string literal, not an object.
If you want a variable that represents an address, you can do that with a String
variable:
Dim a As String
a = "A1"
Dim r1 As Range
Set r1 = Sheet1.Range(a)
Dim r2 As Range
Set r2 = Sheet2.Range(a)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With