PDA

View Full Version : run time error '9' subscript out of range



Me00550
06-18-2019, 08:41 AM
get a run time error '9' subscript out of range

the error occurs on line 6 thru 10

funny thing is this code works most of the time.





Sub CloseMe()Call MessageBoxTimer5
Sheets("Final Use").Range("D11").ClearContents
Worksheets("Sheet2").Range("F1").Copy
Worksheets("Final Use").Range("D13").PasteSpecial Paste:=xlPasteValues
Sheets("Final Use").Range("D11").ClearContents
Sheets("QAT USE").Range("C11").ClearContents
Sheets("QAT USE").Range("C13").ClearContents
Sheets("QAT USE").Range("C15").ClearContents
Sheets("QAT USE").Range("C17").ClearContents
Sheets("QAT USE").Range("C19").ClearContents
Application.DisplayAlerts = False
'ThisWorkbook.Save
'ThisWorkbook.Close
End Sub

Paul_Hossler
06-18-2019, 10:05 AM
Without a failing sample, this is only a guess ...

Make sure that "QAT USE" does not have leading or trailing spaces

Me00550
06-20-2019, 09:21 AM
assuming your meaning spaces in the cells being cleared there are none.

i have it set up so only certain data can be entered in the cells and then if left open for too long the workbook closes automatically resting all cells back to a default value or deletes all data. the error only occurs sometimes. i have even tried to manipulate the program to it force it to produce the error and i cant find out why the error is occurring.

Fluff
06-20-2019, 11:03 AM
That error means that you don't have a sheet called "QAT USE" in the Active workbook

Me00550
06-20-2019, 11:19 AM
there is a sheet called QAT USE

as i said before the program works and then out of nowhere it malfunctions

Fluff
06-20-2019, 11:25 AM
It will work if the workbook that contains the "QAT USE" is the active workbook at run time & it will fail with the error you specified if any other workbook is active when you run the code.