Error Code 9: Subscript out of Range

[LouBalleweg]

I get the subject error when the following line executes.

[With Workbooks(sWorkBookName).Sheets(sSheetName).Columns
(1)]

The Workbook has already been opened. I have confirmed the values of the string variables sWorkBookName and sSheetName for spelling and syntax errors. This same line of code works on one computer but not another. I Thought it might have something to do with file structure, but didn't find anything there either.

 

[JohnYingling]

Wild guess. Do you want the 1st column and are columns numbered from 0 or 1?

http://www.VBResizer.com

Generate Forms/Controls Resizing/Tabbing Class

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[LouBalleweg]

Thanks for the response John.
My columns start with 1.
Here is a copy of the two subs that have to do with the error.
The error occurs in the second sub as noted in remark.

Sub GetNvcLbr()
Application.ScreenUpdating = False
vNvcNmbr = Workbooks("Timecard.xls&quot.Sheets("NvcSht&quot.Cells(1, 2).Value
vNmbr = 0
For Each vName In Range("FdParameters&quot
snRegTm = 0
snOvrTm = 0
sName = vName
If sName = "Lou" Or sName = "Leslie" Then Exit For 'Don't do for these two
sWorkBookName = "C:\Documents\Excel\" & sName & "Time.xls"
sSheetName = sName & "01" 'Search previous year
Call GetIndividualTime
sSheetName = sName & "02" 'Search this year
Call GetIndividualTime
Next vName
Application.ScreenUpdating = True
End Sub
Sub GetIndividualTime()
On Error GoTo NotOpen: ' File isn't open yet
GoTo JmpHere:
NotOpen:
Application.Workbooks.Open sWorkBookName
Resume
JmpHere:
With Workbooks(sWorkBookName).Sheets(sSheetName).Columns(1) ' THIS LINE PRODUCES ERROR 9
On Error GoTo 0
Set v = .Find(vNvcNmbr, LookIn:=xlValues) 'Look for invoice number
If Not v Is Nothing Then
vFirstAddress = v.Address
Do
sAddress = v.Address(, , xlR1C1)
iRow = Val(Mid(sAddress, 2, 5))
snRegTm = .Cells(iRow, 4).Value 'Get regulartime
snOvrTm = .Cells(iRow, 5).Value 'Get overtime
IRowNmbr = iRow
Do 'Get date
IRowNmbr = IRowNmbr - 1 ' Work back up to find date
If IRowNmbr < 1 Then Exit Do 'Searched whole record and didn't find
vType = .Cells(IRowNmbr, 3).Value
If IsDate(vType) = True Then
dLbrDt = vType
Exit Do
End If
Loop
With Workbooks(&quot;TimeCard.xls&quot.Sheets(&quot;NvcSht&quot
iCell = 8
Do ' Find first empty cell
vCellValue = .Cells(iCell, 7).Value
If IsEmpty(vCellValue) Then Exit Do
iCell = iCell + 1
Loop
'ENTER TIME RECORD
.Cells(iCell, 7).Value = dLbrDt
.Cells(iCell, 8).Value = snRegTm
.Cells(iCell, 9).Value = snOvrTm
.Cells(iCell, 10).Value = sName
End With ' NvcSht
Set v = .FindNext(v)
Loop While Not v Is Nothing And v.Address <> vFirstAddress ' Haven't come back to begining
End If ' There is a matching entry in the labor record
End With ' Sheets(sName)
Workbooks(&quot;TimeCard.xls&quot.Sheets(&quot;NvcSht&quot.Activate
End Sub

Maybe this will help.
Thanks again,
Lou

 

[uberpudge]

I'm not sure if this will help as it works on one machine but not the other, but I noticed you have not defined &quot;sSheetName&quot; in the second sub or given it a value.

Have you tried calling the GetIndividualTime() sub while passing &quot;sSheetName&quot; as an argument?

Example:

Sub Sample1()
Dim sSheetName as String

sSheetName = &quot;Some sheet&quot;

Call GetIndividualTime(sSheetName)
End Sub

Sub GetIndividualTime(ByVal sSheetName as String)
'Do stuff with passed argument.
End Sub

Hope this helps,
Pete