PDA

View Full Version : VBA To search for a WorksheetSheet and a Column in that sheet.



RahulA1
01-22-2018, 08:49 AM
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
01-22-2018, 10:27 AM
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
01-22-2018, 12:19 PM
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