matrix20xx
09-27-2007, 06:11 AM
Hi, I am a newbie in VBA. Currently i want to count "PRES CHAMBER" words in ColumnA for all the sheets in a workbook.
I am able to loop all the worksheets but the value return was not the total count for all the worksheets instead it return the value of a particular worksheet where the macro button was situated.
below is the code...
Thanks alot in advance for your help :)
Sub AllSheets()
Dim ws As Worksheet
Dim m As Long
m = 0
For Each ws In ActiveWorkbook.Worksheets
m = WorksheetFunction.CountIf(Cells, "*PRES CHAMBER*")
MsgBox ws.Name
Next
Workbooks("Sheet1.xls").Sheets("Sheet1").Cells(1, 1) = m
End Sub
I am able to loop all the worksheets but the value return was not the total count for all the worksheets instead it return the value of a particular worksheet where the macro button was situated.
below is the code...
Thanks alot in advance for your help :)
Sub AllSheets()
Dim ws As Worksheet
Dim m As Long
m = 0
For Each ws In ActiveWorkbook.Worksheets
m = WorksheetFunction.CountIf(Cells, "*PRES CHAMBER*")
MsgBox ws.Name
Next
Workbooks("Sheet1.xls").Sheets("Sheet1").Cells(1, 1) = m
End Sub