PDA

View Full Version : Just checking the month



thomas.szwed
02-12-2008, 05:32 AM
Hi there,

I want to run a piece of code/statement that brings back all rows of data that contain a certain month in a paticular column. The column it is checking against holds dates in this format> 01.01.2008 or 01/01/2008. If i wanted to bring back say all records that had janurary as their month (i.e. dd/01/yyyy) How would i write the statement to bring back any records aslong as they were in the specified month????

Many Thanks

mdmackillop
02-12-2008, 06:37 AM
Sub CheckMonth()
For Each cel In Selection
tmp = Application.Substitute(cel, ".", "/")
If Month(tmp) = 1 Then
'get data
End If
Next
End Sub

thomas.szwed
02-12-2008, 08:58 AM
Thanks for your reply. This is exactly what i need in principle. Could you help me a little further though?

I would like it to say...

run the sub on sheet 1 and 3 of example.xls

and If the IF statement is true then display that row of data in a new sheet.

That way i would get a new sheet full of data hopefully. Could you provide some code to possibly execute this?

Thanks Again.

Bob Phillips
02-12-2008, 11:06 AM
Public LastRow As Long

Sub CheckMonth()
Dim wsResult As Worksheet

Set wsResult = Worksheets.Add
LastRow = 1
ProcessSheet Worksheets("Sheet1"), wsResult
ProcessSheet Worksheets("Sheet3"), wsResult
End Sub

Private Sub ProcessSheet(ByRef sh As Worksheet, target As Worksheet)
Dim cel As Range
Dim tmp
On Error Resume Next
For Each cel In sh.UsedRange.Columns(1)
tmp = Application.Substitute(cel, ".", "/")
If Month(tmp) = 1 Then
cel.EntireRow.Copy target.Cells(LastRow, "A")
LastRow = LastRow + 1
End If
Next
End Sub

thomas.szwed
02-13-2008, 02:21 AM
How could i code this to run against two sheets in another workbook? (as the workbook i am building will run against a master workbook to retrieve its data....

And what part would i modify for it to look at just one paticular column for the date in both spreadsheets (as there are many dates in the spreadsheet but i am only concerned with column H).

Thanks for all your help

Bob Phillips
02-13-2008, 02:41 AM
Public LastRow As Long

Sub CheckMonth()
Dim wsResult As Worksheet

Set wsResult = Worksheets.Add
LastRow = 1
ProcessSheet Workbooks("other book.xls").Worksheets("Sheet1"), wsResult
ProcessSheet Workbooks("other book.xls").Worksheets("Sheet3"), wsResult
End Sub

Private Sub ProcessSheet(ByRef sh As Worksheet, target As Worksheet)
Dim cel As Range
Dim tmp
On Error Resume Next
For Each cel In sh.UsedRange.Columns(8) 'column H
tmp = Application.Substitute(cel, ".", "/")
If Month(tmp) = 1 Then
cel.EntireRow.Copy target.Cells(LastRow, "A")
LastRow = LastRow + 1
End If
Next
End Sub