VBA error when referencing a defined worksheet

[kgkev]

Hi,

I have isolated a piece of code which has been working 3 years which stopped working.
I have found a work around but I would like to know why the original code wouldn't work

This code no longer works
It gives

Runtime Error '9'
Subscript out of range

Sub test()
Dim MS As Worksheet
Set MS = Sheets("Chasing")
With MS
    .Range("H5", .Range("K5000")).ClearContents
End With
End Sub

This code works and has the desired effect.

Sub test()
With Sheets("Chasing")
    .Range("H5", .Range("K5000")).ClearContents
End With
End Sub

even this works

Sub test()
Dim MS As Worksheet
Set MS = Sheets("Chasing")
With MS
    Sheets("Chasing").Range("H5", .Range("K5000")).ClearContents
End With
End Sub

I use "MS....." throughout the module, probably 30+ instances and all other lines of code still function correctly.

Thanks for helping

 

[Jaafar Tribak]

Are you sure the 'Chasing' Sheet exists in the currently active workbook?

Try fully qualifyng the sheet with its parent workbook ... Something along these lines:

Sub test()
    Dim MS As Worksheet
    Set MS = Workbooks("Your_Workbook_Name_Goes_Here.xlsm").Sheets("Chasing")
    With MS
        .Range("H5", .Range("K5")).ClearContents
    End With
End Sub

 

[kgkev]

I have changed the declaration and it still doesn't work

There are also 3 lines of code which reference MS before the clear contents

    Dim MS As Worksheet
        Set MS = Workbooks("Spares Chasing.xlsm").Sheets("Chasing")

    MS.Range("G1").Value = Now             
    MS.Range("G2").Value = Now
    MS.Range("G2").NumberFormat = "hh:mm"
    MS.Range("H5", MS.Range("K5000")).ClearContents
    MS.Range("A5", MS.Range("Z1000")).Interior.ColorIndex = xlNone
    MS.Range("A5", MS.Range("Z1000")).Font.Color = 1

 

[Jaafar Tribak]

Are you still getting the same error? and on which line exactly ?

 

[kgkev]

same error on the same line.

MS.Range("H5", MS.Range("K5000")).ClearContents

but this works

Sheets("Chasing").Range("H5", MS.Range("K5000")).ClearContents

 

[kgkev]

If I add a "pause" before the code that doesn't and run the suspect code in the immediate window...it works without error.

My head is spinning

 

[Jaafar Tribak]

How about this : (Using the 'WorkSheets' collection instead of 'Sheets')

Sub test()
Dim MS As Worksheet
Set MS = Worksheets("Chasing")
With MS
    .Range("H5", .Range("K5000")).ClearContents
End With
End Sub

 

[kgkev]

That works, Thank you.
Any idea why excel suddenly had a problem with this expression when its worked for years?

 

[Jaafar Tribak]

That works, Thank you.
Any idea why excel suddenly had a problem with this expression when its worked for years?

To be honest, I have no idea but I am glad it worked for you.

 

[Alex Blakenburg]

Just curious any reason you are not just using:

MS.Range("H5:K5000").ClearContents