PDA

View Full Version : Run-time error '9' Subscript out of range.



data
02-03-2015, 09:29 AM
I'm working my way through VBA for dummies, and have come across a dilemma.

When ever I restart my computer and try to run the following code in a new workbook, things work fine. After attempting other macros by overwriting the module, I get get a run-time error '9'.

Now I know this means, excel can't find "Sheet 1", but it looks to me like everything should work. Below is my code:


Sub ShowValue()
contents = Worksheets(“Sheet1”).Range(“A1”).Value
MsgBox contents
End Sub

Attached is a screenshot of what it looks like.

12797

It only seems to work when I first restart my computer. Closing and opening excel seems to change nothing.


Any input would be great. Thanks!

Rob342
02-03-2015, 02:30 PM
Try This


Option Explicit
Sub ShowValue()
Dim ws as Worksheet
Dim Cell as Range
Dim contents as String

Set ws = Worksheets(1)
With ws
contents=ws.range("A1").value
MsgBox contents
End with
End Sub

Rob

data
02-03-2015, 07:22 PM
Rob,

This works! Thank you!

But why?

skywriter
02-03-2015, 10:12 PM
Hi Data, I'm not sure why you are having the problem you are having and I don't know what you mean by this.

After attempting other macros by overwriting the module, I get get a run-time error '9'.


But to give you a little insight into what Rob's code is doing Worksheets(1) refers to the left most worksheet in your workbook. If you have more than one worksheet put a number in cell A1 of the left most worksheet and run the code and you will see that number, now drag the first worksheet to the right so it's no longer the left most worksheet and run the code again, you will notice that if you have a different value in cell A1 of the left most worksheet that value is now what you get in the message box. So if you move the first sheet from it's left most position Worksheets(1) refers to the sheet that is now in that position, it has nothing to do with the tab name "Sheet1".

Paul_Hossler
02-03-2015, 11:11 PM
Possibly related -- The screen shot and the code are using 'smart quotes' (ASCII 147 and 148)and not the regular 'straight quotes' (ASCII 34). Did you paste it in from the book maybe?

You have and look at the quotes around Sheet1



Sub ShowValue()
contents = Worksheets(“Sheet1”).Range(“A1”).Value
MsgBox contents
End Sub



Changing the quote char



Option Explicit
Sub ShowValue()
Dim contents As Variant
contents = Worksheets("Sheet1").Range("a1").Value
MsgBox contents
End Sub


Looks like you're using Office 2013 so it might be OK; I only have 2010 here but with your smart quotes I get a syntax error