PDA

View Full Version : [SOLVED] Subscript out of Range



LutonBarry
03-13-2014, 02:51 PM
Hello hope you can help. I have written some code for a workbook I've named 'WO Report' it is saved as a macro enabled file etc. When I open it I get a message about some unreadable data and do I want to recover it, or some thing like that. Anyway when the file does open it has [Repaired] added to the workbook name in the spreadsheet title bar. This I believe is an excel known fault.

The problem I then encounter is when running the macros it returns a 'Subscript out of range' error when it reaches the last line of the code below. Even if I edit the line and add [Repaired] to the file name in the code it still returns the subscript out of range error. Any ideas folks.

By the way hopefully the code below will appear correctly, apologies if not.


Dim myCheck As Integer
myCheck = MsgBox("Brought to you courtesy of the Luton Outlaws. Locate and open today's Conf Report. Continue?", vbYesNo)
Dim myWorkbook As Workbook
myFile = Application.GetOpenFilename()
Workbooks.Open Filename:=myFile
Set myWorkbook = ActiveWorkbook
Application.Calculation = xlAutomatic
Selection.AutoFilter
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows("WO Report.xlsm").Activate

LutonBarry
03-13-2014, 04:23 PM
Just an update on the error I receive when opening the Workbook file. It says 'Excel found unreadable content in 'WO Report.xlsm'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

If I click yes a further box opens with the message 'Removed records: Sorting from /xl/worksheets/sheet8.xml.part'

SamT
03-13-2014, 04:44 PM
Do you get the Unreadable Content error even if you save and close WO Report before running any macros in it.

Rename WO Reprot to Bad WO Report and start a new WO Report.xlsm

Open both by double clicking on them.

Manually copy all the cells on each sheet in Bad WO Report into the sheets in the new WO Report.

Manually copy all the code from Bad into the New book.

LutonBarry
03-13-2014, 04:51 PM
Sam thanks I'll give that a go in the morning and update this if it works. It does seem to be a random error as the original worked fine.

LutonBarry
03-14-2014, 09:49 AM
SamT very grateful that did the trick to recover the position. But strangely every now and then it happens again and I can't understand why.

Bob Phillips
03-14-2014, 11:22 AM
When I get this, I usually answer no, then shutdown Excel. Restarting Excel and opening the file overcomes that instance.

You might also try Rob Bovey's Code Cleaner on the file, cures all manner of ailments :)