1

How do you correct assigna variable to store the reference to the cell ? Last line throws Subscript out of range error

Public Sub CommandButton1_Click()

Dim variance As Range
Dim risk As Range
Dim time As Range
Dim output As Range

variance = Sheets("ABC").Range("C6")
Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
Karusmeister
  • 871
  • 2
  • 12
  • 25
  • 3
    You have to use the word `Set` to set the range in that variable. `Set variance = Sheets("ABC").Range("C6")` – Siddharth Rout Sep 27 '13 at 09:42
  • 1
    See this http://stackoverflow.com/questions/349613/what-does-the-keyword-set-actually-do-in-vba AND http://stackoverflow.com/questions/3872339/what-is-the-difference-between-dim-and-set-in-vba – Siddharth Rout Sep 27 '13 at 09:44
  • @SiddharthRout This also wrong in OP code, but that would be a error 91 "Object variable or With block not set". Actual problem is another. – LS_ᴅᴇᴠ Sep 27 '13 at 09:46
  • Sheet "ABC" exists? Are you running this from a form? Note `Sheets` defaults to `Application.Sheets` which "Returns a Sheets collection that represents all the sheets in the active workbook" – LS_ᴅᴇᴠ Sep 27 '13 at 09:48
  • 1
    @LS_dev: I guess that is a typo from the OP. :@Karusmeister: Once you change the above code, if you get the `Subscript out of range error` then check if your sheet name is actually"ABC" – Siddharth Rout Sep 27 '13 at 09:49
  • I guess OP doesn't really have a sheet named "ABC" in place... – sam092 Sep 27 '13 at 10:00
  • true @ Sam/LS. OP has 2 problems in the above code. `1` There is no sheet with the name "ABC" and `2` In correct way of assigning to range variable. – Siddharth Rout Sep 27 '13 at 10:16

1 Answers1

2

Set variance = Sheets("ABC").Range("C6")

+

What is the difference between dim and set in vba

+

Subscript out of range means you do not have a sheet named ABC or the spelling/casing is different. Make sure it's not an Abc or ABc etc.

Community
  • 1
  • 1