Object Variable or With Block Variable not set

[Yorke]

Hi all,

I'm having problems with a simple piece of code which suddenly stopped working, and I don't recall changing anything which has caused this to break. The code says 'object variable or with block variable not set', but I have set the dim above. Here is my code:

Dim ThisWorkBook As Workbook
Dim Cell2 As Range

For Each Cell2 In ThisWorkBook.Worksheets("Data").Range("Z91:Z186") 'Debug highlights this line

'Long piece of additional code

Next Cell2

Thanks in advance

 

[Joe4]

Note that "ThisWorkbook" is NOT a variable, so you should NOT declare it in your code (if you aren't setting it, it is not a variable).
It is a pre-defined object in VBA.

Some things to check:
1. Is the sheet still named "Data", or has it been renamed?
2. Is the "Data" sheet visible?
3. Is the "Data" sheet protected?
4. Are any of the cells in the range Z9:Z186 locked/protected, hidden, or merged?
5. Do any of the cells in the range Z9:Z186 contain errors?

 

[Joe4]

So which one was it?

 

[Yorke]

Hi Joe,
Thank-you for your help!
I simply removed the dimensions from the workbooks.
Thanks again!

 

[Joe4]

You are welcome.