VBA point variable to range

Question

Hi

I want to point to a cell as a range in VBA. I've tried using:

         Dim range
         range = Sheet("sheet").Range("A1")

But this just returns the value in the range. What I actually want is the range object so I can manipulate it, e.g. by setting range.Value = "Hello"

Any ideas?

Answer 1

First, I strongly recommend you to make explicit declaration of variables in your code mandatory. Go to Tools - Options, in the Editor tab check "Require variable Declaration", or put Option Explicit in the first line of all your scripts.

Second, I think there is a small typo in your code, it should be Sheets.("sheet").

To answer your question, with range = Sheets("sheet").Range("A1") you are assigning a value variable, not an object. Therefore the default variable of the range object is implicitly assigned, which is value. In order to assign an object, use the Set keyword. My full example code looks like this:

Option Explicit

Public Sub Test()
    Dim RangeObject As range
    Set RangeObject = Sheets("Sheet1").range("A1")    
    RangeObject.Value = "MyTestString"
End Sub

This should put the text "MyTestString" in cell A1.

Edit: If you are using named ranges, try RangeObject.Value2 instead of RangeObject.Value. Named ranges do not have a Value property.