sorting dates

[tommy]

Hi , I have a spread sheet that I use to indicate when a two monthly job
needs doing, a date is put into cell B3 and dates in two monthly gaps are put
into B4 B5 B6 for twelve columns and for ten rows, each row starts with a
different date so they are quite mixed up in the columns , if I name the
columns Jan/Feb/ March and so on, is there any way that I can get all the
dates that are in January to populate the column marked Jan and all the dates
that are in February to populate the Feb column . The dates read ( 12 march
2008 ) with the cell formatted to read that way.
Thanks in advance,
Tommy

 

[Joel]

You need a macro. The macro assumes the data is in cells B4:M15 (change as
required) and that Jan - Dec is in Row 1 and the months are in columns where
no other data is located.

Sub ArrangeDates()

Set DateRange = Range("B4:M15")
For Each cell In DateRange
MyMonth = Format(cell, "mmm")
Set c = Rows(1).Find(what:=MyMonth, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

If c Is Nothing Then
MsgBox ("Cannot find month : " & MyMonth)
Else
LastRow = Cells(Rows.Count, c.Column).End(xlUp).Row
Newrow = LastRow + 1
Cells(Newrow, c.Column) = cell
End If
Next cell

'sort column Data
For MonthNum = 1 To 12
MyMonth = Format(MonthNum & "/1/" & Year(Date), "mmm")
Set c = Rows(1).Find(what:=MyMonth, _
LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)

If c Is Nothing Then
MsgBox ("Cannot find month : " & MyMonth)
Else
LastRow = Cells(Rows.Count, c.Column).End(xlUp).Row
Range(c, Cells(LastRow, c.Column)).Sort _
key1:=c, _
order1:=xlAscending, _
header:=xlYes
End If

Next MonthNum


End Sub

 

[tommy]

Thanks where do I put the Macro, is it right click on XL and view code?

 

[tommy]

Sorry joel I have lead you astray B4 B5 B6 should read C3 D3 E3 for twelve
columns and ten rows from B3 to B13.

 

[Joel]

Your description wasn't consistent with the number of rows and columns that
is why I made it easy to change

from:
Set DateRange = Range("B4:M15")
to
Set DateRange = Range("B3:N15")


You put the code in the VBA page. there are a number of way to get to the
VBA. the easiest is to type Alt-F11. for the code to work on every page it
is best to put into a MODULE page. Add a module into the VBA window using
the menu in VBA Insert - Module. then copy the code from the posting to the
VBA window.

 

[tommy]

Thank you Joel.