Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Cells that meet criteria across entire workbook
I am looking to write a macro that checks each cell in each sheet in workbook and counts the occurences of formulas. I am not sure how t approach this. The code below prints all the sheets in a workbook. It dimensions "s" as a Worksheet. Is it possible to use the sam thinking for what I want to do? That is, can you dimension a variabl as a cell (or range), check to see that it meets a condition, and kee adding this to a cumulative total that summarizes the result in message box? Sub PrintAll() Dim s As Worksheet For Each s In Worksheets() s.Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Next s End Sub Thank -- ExcelMonke ----------------------------------------------------------------------- ExcelMonkey's Profile: http://www.excelforum.com/member.php...nfo&userid=522 View this thread: http://www.excelforum.com/showthread.php?threadid=26821 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Cells that meet criteria across entire workbook
Sub CountAll()
Dim s As Worksheet Dim cell as Range Dim cnt as Long cnt = 0 For Each s In Worksheets() for each cell in s.UsedRange if isnumeric(cell) then if cell.Value 100 then cnt = cnt + 1 end if end if Next cell Next s Msgbox "Number found is " & cnt End Sub If you are actually counting against a simple criteria, you would probably want to use CountIF or Sumif against the usedrange. More explanation, more help. -- Regards, Tom Ogilvy "ExcelMonkey" wrote in message ... I am looking to write a macro that checks each cell in each sheet in a workbook and counts the occurences of formulas. I am not sure how to approach this. The code below prints all the sheets in a workbook. It dimensions "s" as a Worksheet. Is it possible to use the same thinking for what I want to do? That is, can you dimension a variable as a cell (or range), check to see that it meets a condition, and keep adding this to a cumulative total that summarizes the result in a message box? Sub PrintAll() Dim s As Worksheet For Each s In Worksheets() s.Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Next s End Sub Thanks -- ExcelMonkey ------------------------------------------------------------------------ ExcelMonkey's Profile: http://www.excelforum.com/member.php...fo&userid=5221 View this thread: http://www.excelforum.com/showthread...hreadid=268211 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting # of cells with that meet criteria in two columns | Excel Worksheet Functions | |||
Counting Consecutive Cells that meet Criteria | Excel Discussion (Misc queries) | |||
Counting lines that meet TWO criteria | Excel Worksheet Functions | |||
Counting cells that meet 2 differnet criteria | Excel Worksheet Functions | |||
Counting Values that meet another cells criteria | Excel Worksheet Functions |