With Workbooks(xxx) - Subscript out of Range?!

[JamesW]

Hey guys,

I can't get my head round this, doesn't help that it's Friday afternoon and it's roasting outside...

Problem part is bolded.

It runs fine when I use it in the main sheet, but when I try using a WITH it ****s up..

Option Explicit
Dim lRow, i, j As Integer
Dim FileToOpen As String
Sub test()
    Application.ScreenUpdating = False

    FileToOpen = Application.GetOpenFilename _
    (Title:="Please choose the latest APO file", _
    FileFilter:="Excel Files *.xls (*.xls),")
    
    If FileToOpen = "" Then
        MsgBox "No file specified."
        Exit Sub
    Else
        Workbooks.Open Filename:=FileToOpen
    End If

    [U][B]With Workbooks(FileToOpen).Sheets("Sheet1")[/B][/U] 'OOR ERROR
        lRow = .Range("AL" & Rows.Count).End(xlUp).Row
        For i = 2 To lRow
            For j = 38 To 193
                If .Cells(i, j).Value <> 0 Then
                    If j = 38 Then
                        .Range("AK" & i).Value = 0
                        Exit For
                    Else
                        .Range("AK" & i).Value = Cells(1, j).Value
                        Exit For
                    End If
                Else
                    .Range("AK" & i).Value = 0
                End If
            Next j
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

 

[Jonmo1]

Try like this

Dim MyBook As WorkBook
 
If FileToOpen = "" Then
        MsgBox "No file specified."
        Exit Sub
    Else
        Set MyBook = Workbooks.Open(Filename:=FileToOpen)
    End If
 
    With MyBook.Sheets("Sheet1")

Hope that helps.

 

[JackDanIce]

This may not be the answer you want, but your code implies (I think!) the workbook FileToOpen will be (at that stage of excecution) the active workbook so have you tried changing your WITH statement to just?

With Sheets("Sheet1")

 

[JamesW]

Brilliant, thanks Jonmo.

Jack, good point, I didn't think about that. I'll add them both to my ever growing VBA reference folder