Run-Time Error '9' Subscript out of range

[gencom99]

I am using a spreadsheet. It has two tabs MAIN and TOTAL CASH. On the MAIN tab is a BUTTON and a cell where to enter a number. After you put a number in the cell and click the button, the results of the query is in the tab TOTAL CASH


Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("TOTAL CASH")
Numb = Range("H6")


If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("T_CASH")
Query.Sql = "SELECT CUST_TBLE.NAME, CASH_TBLE.SAV_AMT " _
& "FROM JES.CUST TBLE CUST TBLE,
JES.CASH_TBLE CASH_TBLE" _
& "WHERE CUST_TBLE.NUM_ID = CASH_TBLE.NUM_ID AND CUST_TBLE.NUM_CODE IN (" & NumList & ") "




Query.Refresh (False)


End Sub

This works.

In a new workbook, I created two tabs, MAIN and MONEY. On the MAIN tab is a BUTTON and a cell where to enter a number. After you put a number in the cell and click the button, I want the results of the query in the tab TOTAL CASH. This is not working. This code is very simiar to the code above that works. The only difference is the query. I am getting this error: run-time error '9'-- Subscript out of range. This is the line with the error:
Set Query = QuerySheet.QueryTables("MNY")

Why does one code work, and the other code does not?

This is the code that does not work.

Private Sub CommandButton1_Click()
Dim NumList As String
Dim SourceBook As Workbook
Dim ListSheet As Worksheet
Dim QuerySheet As Worksheet
Dim Query As QueryTable
Dim Numb As String

Set SourceBook = Application.ActiveWorkbook
Set ListSheet = SourceBook.Worksheets("Main")
Set QuerySheet = SourceBook.Worksheets("MONEY")
Numb = Range("H6")


If ListSheet.Cells(6, 8).Value = "" Then Exit Sub
NumList = "'" & ListSheet.Cells(6, 8).Value & "'"

Set Query = QuerySheet.QueryTables("MNY")
Query.Sql = "SELECT NUMB_TBLE.NUMB_NAME, extract(month from NUMB_DATES_TBLE.DAY_MTH_YR) " _
& "FROM JES.NUMB TBLE NUMB TBLE,
JES.NUMB_DATES_TBLE NUMB_DATES_TBLE" _
& "WHERE NUMB_TBLE.NUMB_ID = NUMB_DATES_TBLE.NUMB_ID AND NUMB_TBLE.NUMB_CODE IN (" & NumList & ") " _
& "Group by numb_tble.numb_name,
extract(month from NUMB_DATES_TBLE.DAY_MTH_YR)




Query.Refresh (False)


End Sub

 

[SkipVought]

Hi,

You never disclosed exactly WHAT STATEMENT your procedure errored on.

Chances are it is one of the Worksheet statements.

You stated, "It has two tabs MAIN and TOTAL CASH."

Your code has TWO possible logical errors...

Set ListSheet = SourceBook.Worksheets("Main")
...
Set QuerySheet = SourceBook.Worksheets("MONEY")
...

MAIN is not equal to Main
and do you really have a THIRD sheet named MONEY???


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

might want to check that your querytable is actually called "MNY"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MarkWalsh]

This is cross posted at:

http://www.vbforums.com/showthread.php?t=584755