PDA

View Full Version : Count --> all worksheets



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

xld
09-27-2007, 06:24 AM
Sub AllSheets()

Dim ws As Worksheet
Dim m As Long

m = 0

For Each ws In ActiveWorkbook.Worksheets

m = m + WorksheetFunction.CountIf(ws.Cells, "*PRES CHAMBER*")

MsgBox ws.Name
Next

Workbooks("Sheet1.xls").Sheets("Sheet1").Cells(1, 1) = m

End Sub

matrix20xx
09-27-2007, 06:29 AM
Thanks xld for your help...