Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing all sheets in a workbook in VBA
Private Sub Counting_Test()
Dim count As Single count = 0 For Each c In Workbooks("June.xls").Worksheets("11").Range("H1:H 275") If c.Value = Date Then count = count + 1 Next MsgBox count End Sub Ok, when this macro is run, it'll give me a message box telling me ho many cells in workbook "June" bewteen H1 and H275 on worksheet "11 have today's date in the in them. This works just fine, however, I nee to get a way so that every cell between H1 and H275 on every workshee in the workbook is counted, not just one worksheet....Can you all hel me? PS. the "Sheets(Array" term can't be used because that always refers t specific sheets. This workbook is updated daily, so I can't refer t specific sheets. Thanks in advanc -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing all sheets in a workbook in VBA
Hi
try Private Sub Counting_Test() Dim wks as worksheet Dim count As Single count = 0 for each wks in activeworkbook.worksheets count = count + application.countif(wks.range("H1:H275"),Date) next MsgBox count End Sub -- Regards Frank Kabel Frankfurt, Germany Private Sub Counting_Test() Dim count As Single count = 0 For Each c In Workbooks("June.xls").Worksheets("11").Range("H1:H 275") If c.Value = Date Then count = count + 1 Next MsgBox count End Sub Ok, when this macro is run, it'll give me a message box telling me how many cells in workbook "June" bewteen H1 and H275 on worksheet "11" have today's date in the in them. This works just fine, however, I need to get a way so that every cell between H1 and H275 on every worksheet in the workbook is counted, not just one worksheet....Can you all help me? PS. the "Sheets(Array" term can't be used because that always refers to specific sheets. This workbook is updated daily, so I can't refer to specific sheets. Thanks in advance --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing all sheets in a workbook in VBA
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing all sheets in a workbook in VBA
Hi
thanks for your feedback. Just as additional note: This could also be achieved with (complicated) worksheet functions. But if you're happy with VBA, use it :-) -- Regards Frank Kabel Frankfurt, Germany Hah!, where's like a charm! thanks Frank! :) --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing sheets | Excel Discussion (Misc queries) | |||
Formulas referencing different sheets in workbook | Excel Discussion (Misc queries) | |||
When referencing other Sheets | Excel Discussion (Misc queries) | |||
Referencing Different Sheets in VBA | Excel Discussion (Misc queries) | |||
Referencing Sheets | Excel Programming |