Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA for Excel - Setting a range

Tags:

excel

vba

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!

like image 300
mathstackuser123 Avatar asked Mar 15 '17 14:03

mathstackuser123


People also ask

How do I assign a range in Excel VBA?

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.

How do you declare a range variable in VBA?

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.


2 Answers

You could use Address:

worksheets("specificworksheet").range(r.Address).value=1 
like image 30
Scott Craner Avatar answered Sep 21 '22 18:09

Scott Craner


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)
like image 111
Mathieu Guindon Avatar answered Sep 21 '22 18:09

Mathieu Guindon