View Full Version : Runtime Errror 9 Subscript out of range
phan913
12-31-2014, 01:36 PM
I need some help I am trying to automatically sort 2 columns B and C when I update another sheet. When I update another sheet, sheet 1 updates through a formula but I keep getting Runtime Errror 9 Subscript out of range. Any help will be appreciated.
thanks,
Phan
12670
Private Sub Worksheet_Calculate()
ThisWorkbook.Activate
Application.Workbooks("ThisWorkbook").Sheets("1").Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub
Kenneth Hobs
12-31-2014, 01:48 PM
You have an open workbook with a name of "ThisWorkbook"? ThisWorkbook is an object of the current workbook. Just use ThisWorkbook or even ActiveWorkbook if needed. In a like manner, you have a sheet named, "1"? While that is possible, the usual is "Sheet1". Whatever string value you have the sheet's tab, that is what you use for the string for Sheets() or WorkSheet().
I don't see a need to activate the current workbook which would be ThisWorkbook.
I can be more exact if you would work up a very simple workbook and attach it.
Paul_Hossler
12-31-2014, 01:57 PM
I think you meant
Private Sub Worksheet_Calculate()
ThisWorkbook.Activate
ThisWorkbook.Sheets("1").Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub
Paul_Hossler
01-01-2015, 11:03 AM
Although a lot of that is probably redundant since Excel's assumed defaults are usually pretty accurate
Private Sub Worksheet_Calculate()
Sheets("1").Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub
If Sheets("1") module contains the code, then this should also work
Private Sub Worksheet_Calculate()
Range("B6:C100").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes
End Sub
Bob Phillips
01-01-2015, 11:25 PM
Personally, I never trust the defaults, I would rather be fully explicit myself, full qualify ranges, and cancel events
Private Sub Worksheet_Calculate()
On Error GoTo wsc_exit
Application.EnableEvents = False
With ThisWorkbook.Worksheets("Sheet1")
.Range("B6:C100").Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlYes
End With
wsc_exit:
Application.EnableEvents = True
End Sub
phan913
01-05-2015, 07:19 AM
Thank you everyone for your suggestions. I was able to get it to work.
Thanks again,
Phan Nguyen
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.