VBA To search for a WorksheetSheet and a Column in that sheet.

[RahulA1]

Hi,
I have an Excel workbook with about 10 different sheets and some sheet names have ACCT at the start or end of their name (For ex one sheet name is XYZ_ACCT and other sheet name is ACCT_ABC). In each of these ACCT sheets there is a column with a name ACCT_No. I would like to know if there is a way to search for the sheets whose name have ACCT in it (For ex one sheet name is XYZ_ACCT and other sheet name is ACCT_ABC) and then go to the Column ACCT_No in that sheet and count the number of cells which have values in that column.

[p45cal]

Sub blah()
Dim colmHeader As Range
For Each sht In ActiveWorkbook.Sheets
  If InStr(1, sht.Name, "ACCT", vbTextCompare) > 0 Then
    Set colmHeader = sht.Rows(1).Find(What:="ACCT_No", LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False)  'only searches row 1.
    'Set colmHeader = sht.Cells.Find(What:="ACCT_No", LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False)'searches whole sheet.
    If Not colmHeader Is Nothing Then
      ColumnLetter = Split(Cells(1, colmHeader.Column).Address, "$")(1)
      MsgBox "Sheet " & sht.Name & " has a column with 'ACCT_No' in it and that column (" & ColumnLetter & ") has " & Application.WorksheetFunction.Count(colmHeader.EntireColumn) & " numbers in it."  'count cells with numbers in.
      'MsgBox "Sheet " & sht.Name & " has a column with 'ACCT_No' in it and that column (" & ColumnLetter & ") has " & Application.WorksheetFunction.CountA(colmHeader.EntireColumn) & " values in it (incl. the header itself)." 'counts cells which aren't empty.
    Else
      MsgBox "FYI:" & vbLf & "Sheet " & sht.Name & " has no column with 'ACCT_No' in it"
    End If
    Set colmHeader = Nothing
  End If
Next sht
End Sub

[RahulA1]

Thanks a lot for the solution. It works !!! Although i need to change the code to make it run from a new workbook which will have a location for the workbook on which I need to run this code, I think I can try doing that. Thanks a to again. Really appreciate the help !!

Sub blah()
Dim colmHeader As Range
For Each sht In ActiveWorkbook.Sheets
  If InStr(1, sht.Name, "ACCT", vbTextCompare) > 0 Then
    Set colmHeader = sht.Rows(1).Find(What:="ACCT_No", LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False)  'only searches row 1.
    'Set colmHeader = sht.Cells.Find(What:="ACCT_No", LookIn:=xlFormulas, LookAt:=xlWhole, SearchFormat:=False)'searches whole sheet.
    If Not colmHeader Is Nothing Then
      ColumnLetter = Split(Cells(1, colmHeader.Column).Address, "$")(1)
      MsgBox "Sheet " & sht.Name & " has a column with 'ACCT_No' in it and that column (" & ColumnLetter & ") has " & Application.WorksheetFunction.Count(colmHeader.EntireColumn) & " numbers in it."  'count cells with numbers in.
      'MsgBox "Sheet " & sht.Name & " has a column with 'ACCT_No' in it and that column (" & ColumnLetter & ") has " & Application.WorksheetFunction.CountA(colmHeader.EntireColumn) & " values in it (incl. the header itself)." 'counts cells which aren't empty.
    Else
      MsgBox "FYI:" & vbLf & "Sheet " & sht.Name & " has no column with 'ACCT_No' in it"
    End If
    Set colmHeader = Nothing
  End If
Next sht
End Sub