Try something like

Dim Sh As Variant
Dim N As Long
Dim WB As Workbook
Sh = Array("Book2", "Book3", "Book99")
For N = LBound(Sh) To UBound(Sh)
On Error Resume Next
Set WB = Nothing
Set WB = Application.Workbooks(Sh(N))
If Err.Number = 0 Then
Debug.Print "book '" & WB.Name & "' is open."
Else
Debug.Print "book '" & Sh(N) & "' is not open."
End If
Next N



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com



On Sun, 28 Mar 2010 20:03:37 -0700 (PDT), Len
wrote:

Hi,

Codes below try to check every existing workbook's name currently
opened is correct and if not, it exit sub
After several attempts, it still not able to run and prompts "
Subscript out of range"

Sub test()
Dim y As Long, x As Variant
x = Array("AB.xls", "CD.xls", "EF.xls", "GH.xls")
For y = LBound(x) To UBound(x)
If Workbooks(x(y)).Name = "AB.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
ElseIf Workbooks(x(y)).Name = "CD.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
ElseIf Workbooks(x(y)).Name = "EF.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
ElseIf Workbooks(x(y)).Name = "GH.xls" Then
MsgBox Workbooks(x(y)).Name & " not found "
Exit Sub
End If
Next y
End Sub

Any helps will be appreciated and thanks in advance

Regards
Len