CODEWITHSUNDEEP
excelvbaruntime-error
Zac Grafton
Zac Grafton

Reputation: 3

VBA Run-time error: '9'

I am trying to create a a function that takes some inputs of selected position and day and then using the day input, scan the matching sheet for data. However, passing a string to Sheets.Activate is causing me to get

runtime error 9

I am not sure why this is happening as other people did this in similar solutions on stack overflow. Here is my code

Sub GatherNames()

Dim x As Integer
Dim y As Integer
Dim rowN As Integer
Dim cell As String
Dim day As String
Dim position As String

day = ThisWorkbook.Sheets("Coverage").Range("C3")

Sheets(day).Activate

Application.ScreenUpdating = False

'Acquire entire data range
NumRows = Range("B3", Range("B3").End(xlDown)).Rows.Count
NumRows = NumRows / 2
'Start at beginning of data
rowN = 3
cell = "B" & rowN
Range(cell).Select
'Run for loop to check all rows
For y = 1 To NumRows
    'Get Rows Value
    Range(cell).End(xlToRight).Select

    'Add to list
    If (ActiveCell.Value > 1) Then
        Debug.Print ActiveCell.Value
    End If

    'Reset Cell to beginning of next Row
    rowN = rowN + 2
    cell = "B" & rowN
    Range(cell).Select
Next
Application.ScreenUpdating = True

End Sub

I have tested all the code while excluding grabbing the day and then activating the day sheet and it works fine, but when I try and take in a string and then go to that sheet using 

Sheets(day).Activate

it causes

run time error 9 - subscript out of range

For extra detail, the value of day is "Monday" and I have a sheet called Monday.

Upvotes: 0

Views: 1124

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

Day is a Reserved Word so you can't use it as a variable. Change all references to that variable to something else, like myDay.

In the VB Editor, press F2 to open the Object Browser: basically none of the listed should be used for naming objects/variables/sheets/etc, as they are reserved for VBA.

If you're still having a problem, try, on the modified line (using myDay for example), after this:

myDay = ThisWorkbook.Sheets("Coverage").Range("C3")

...add a line:

MsgBox "--" ActiveSheet.Name & "--" & vbcrlf & "--" & myDay& "--"

What does it say when you run the code again?

  • Are the 2 lines identical?

  • If you get an error, please include the full text or a screenshot of the error and where it is breaking the code.

Upvotes: 1

Related Questions