PDA

View Full Version : Solved: Aggregate records entered for 2008



Eric58132
06-26-2009, 10:40 AM
Good afternoon,

I've been tasked with something that is a little over my head but I know it's simple for some of you :) .


There is a workbook with roughly 250 tabs in it, containing random data columns. I need to find the aggregate number of records that were entered in 2008. Here's the catch: the data isn't all formatted entirely the same. The Date field (which is actually called Date), either lies in column B or column C.

What I'm looking to do in VBA is say:

For each sheet, find out if the word "Date" resides in cell B1 or C1, retrieve the number of records that occurred in 2008 within that column, and find the sum for all of the sheets combined.

Can anyone help me out?

georgiboy
06-26-2009, 11:54 AM
Try this...

Sub ShCounter()
Dim ws As Worksheet, Col As Long
Dim Total As Long, rCell As Range
Dim ToFind As Long

ToFind = InputBox("Input year to find. Example = 2008", "Input needed")

For Each ws In ThisWorkbook.Worksheets
Col = ws.Rows(1).Find("Date").Column
EndRow = ws.Cells(Rows.Count, Col).End(xlUp).Row
For Each rCell In ws.Range(ws.Cells(2, Col), ws.Cells(EndRow, Col)).Cells
If Year(rCell.Value) = ToFind Then
Total = Total + 1
End If
Next rCell
Next ws

MsgBox Total

End Sub
Hope this helps

Eric58132
06-26-2009, 12:13 PM
hi Georgiboy,

Thank you for your efforts. This seems to be exactly what I was looking for One small thing.... (I hope). The dates are currently formatted in dd-Month-yy format.

For example, 08-May-09, 20-Mar-09, etc.

Any ideas? And thank you a TON in advance.

p45cal
06-26-2009, 12:25 PM
I don't think it matters, have you tested it?

Eric58132
06-26-2009, 12:26 PM
I have....I get an error and when checking where it occurs on the following line:

If Year(rCell.Value) = ToFind Then

georgiboy
06-26-2009, 12:41 PM
I can't recreate the error, could you post a workbook with dummy data to make for an easier solution?

Eric58132
06-26-2009, 12:46 PM
certainly.

Here it is:

p45cal
06-26-2009, 12:51 PM
when the code stops, click 'Debug' and in the immedaite pane type:
rcell.select
and press Enter, then look at the active sheet and see what is selected. Is it a valid date?
you could also type:
?rcell.value
and see if it's a date.
Finally if looks like a date, it may not be!; check this with either
?isdate(rcell.value)
or
?typename(rcell.value)
both should return true if it's a date.

Added later: I've tried your sample sheet and apart from falling over because one of the sheets doesn't have the word 'Date' in the top row (sheet 3) it's fine.

Eric58132
06-26-2009, 01:09 PM
hmm.... when I type ?rcell.value I'm given "3/230/05"...which clearly doesn't look like a date (though close). The thing is, I don't see where that error occurs in my spreadsheet and if I type rcell.select, I get a mismatch error.

I'm starting to think it might be best to just tell them the data isn't formatted properly for this whole thing to work. The code written and suggestions you have all given have been great. Thank you for your efforts.

georgiboy
06-27-2009, 07:17 AM
If you add this
On Error Resume Next before the ws loop then it should solve the problem of having worksheets without a "Date" header in row 1.

You could also add the same line of code before the rCell loop but if you do be sure to check the results manualy after to make sure it is getting the correct result. If there is anything other than a date in the rCell it is currently searching then it will pass over it and not count it, it shouldent produce an error.

Hope this helps

Aussiebear
06-27-2009, 03:06 PM
Use the "find" function to locate the value "3/230/05". It will tell you the location.

Eric58132
06-30-2009, 06:48 AM
thank you to everyone for your help! Great teamwork, this forum kicks ass.... seems to be the best outlet for learning vba I've been able to find, that's for sure.