sorting dates

  • Thread starter Thread starter tommy
  • Start date Start date
T

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
 
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
 
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.
 
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.
 
Back
Top