Cannot set range variable in VBA

Question

I'm brand new to VBA and trying to learn variables. I have a simple piece of code that should set a variable to a range:

Sub Test()
    Dim DataRange As Range
    DataRange = Workbooks("Rooms.csv").Sheets(1).Range("A3", "AK17068")
End Sub

When I run I get Run-time error '91': Object variable or With block variable not set which.... isn't that what I did with the Dim statement? What is this referring to?

I saw in the MS docs that in certain cases (I don't fully understand when) I should use Option Strict On but when I try to use this I get a syntax error (Expected: Base or Compare or Explicit or Private). I think I am fundamentally misunderstanding how this should work (am C++ dev by training)

Damian · Accepted Answer · 2019-03-20 15:34:47Z

8

Here:

Sub Test()
    Dim DataRange As Range
    Set DataRange = Workbooks("Rooms.csv").Sheets(1).Range("A3", "AK17068")
End Sub

ObjectVariables are Set with Set

answered Mar 20, 2019 at 15:34

Damian

5,1741 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Badja · Accepted Answer · 2019-03-20 15:34:57Z

4

You need to set the data range

Set DataRange = Workbooks("Rooms.csv").Sheets(1).Range("A3", "AK17068")

answered Mar 20, 2019 at 15:34

Badja

8751 gold badge10 silver badges36 bronze badges

Comments

Error 1004 · Accepted Answer · 2019-03-20 17:32:37Z

1

Using With Statement referring to the workbook & worksheet we are getting the advantage of not repeating workbook & worksheet names if you are planning to set more ranges. See below:

Option Explicit

Sub Test()

    Dim DataRange As Range, DataRange2 As Range

    With Workbooks("Rooms.csv").Worksheets("Sheet1") '<- Using *With Statement* referring to the workbook & worksheet
        Set DataRange = .Range("A3", "AK17068")
        Set DataRange2 = .Range("A1") '<- For example purpoces
    End With

End Sub

answered Mar 20, 2019 at 17:32

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

Cannot set range variable in VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related