Click here to Skip to main content
14,828,290 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want an excel file in which there are column headers stated "Employee ID", "Name", "Area", "City" and "State", not necessary in same order. I also have some sheets in same wbk which have few of the above headers, but not all of them. I want to find such sheet where the column have all the mentioned headers, irrespective of the order. I tried coding it, but got an error on the line:
For each ws in Workbooks("Trials.xslm").Worksheets

I got an error of <Subscript out of range>.
Kindly throw a light on it.

What I have tried:

Private Sub ValidButton_Click()
    Dim nColumn As Double
    Dim ws As Worksheet
    Dim i As Integer
    Dim strA As String, strB As String, strC As String, strD As String, strE As String
    strA = "Employee ID"
    strB = "Name"
    strC = "Area"
    strD = "City"
    strE = "State"
    For Each ws In Workbooks("Trials.xslm").Worksheets
        For i = 1 To UsedRange.Columns.Count
            If Cells(1, i).Value = strA Or strB Or strC Or strD Or strE Then
                MsgBox True
                MsgBox False
            End If
        Next i
End Sub
Updated 11-Jan-18 9:16am

1 solution

I got an error of <Subscript out of range>.

This means that Trials.xslm is closed. You have to open it before you start doing anything else.

Dim wbk As Workbook

Set wbk = Application.Workbooks,Open("FullPathAndFilename.xlsx")

For Each ws In wbk.Worksheets
'your code here

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900