Peter, here's the process I'm using to figure out how to write the code.
1) Determine what is needed
- Bottom Cell + 1 in J to start the Find from
- Bottom Cell in I
- MAX(Cell in I 30 days before Bottom of I OR Top cell in I)
- Const NumRecords = 1
- Const RecordWidth = 7 (columns)
- Relevant Range (From MAX to Bottom + 1)
- Allow for skipped dates and sections
2) Decide on the basic algorithm
- Iterate down Relevant Range (Cel)
- Find xlUp Relevant Range
- Stop when Cel.Address = Found.Address
3)Guess at future possibilities
- more than one months records.
- Auto-compress outdated section to totals
- Auto-insert new Day Section
4 Determine what knowledge is still needed (you tell me the answers.)
- Determine macro trigger (Manually Triggered From Menu?)
In keeping with 3), we'll use a function to determine the bottom cell in Column J, Since we're using a function for one bottom cell, we'll use it of the bottom used cell in both columns. To enable the Auto-compress possibility, We will use a function to find the Section Date cell that is 30 days before the last section date. This changes the MAX used above to If Found Is Nothing Then earliest date is Cell("I2"). Auto-Compress can use Date - 31 to find section to compress or delete
First we'll write the two functions that will be used in the rest of the code.
Private Function BottomCell(Col As Long) As String
BottomCel = Cells(Rows.Count, Col).End(xlUp).Address
End Function
That reminds that in order for you to easily rearrange your spreadsheet, we should use Constants for the columns.
Const SecHeadCol As String = "I"
Const DataDateCol As String = "J"
Const TopSecHeadAddress As String = "I2"
Const TermOfProjections As Long = 30
While writing the StartingSectionHead Function I saw the need for Two relevant Range Functions
Private Function SectionHeadsRange() As Variant
'Returns entire range whether 30 days, a Quarter, or the whole year
Set SectionHeadsRange = Range(Range(TopSecHeadAddress), _
Cells(Rows.Count, SecHeadCol).End(xlUp))
End Function
[Private Function StartingSectionHead() As Variant
Dim Found As Range
Dim BottomCel As Range
Set BottomCel = Cells(Rows.Count, SecHeadCol).End(xlUp)
Set Found = SectionHeadsRange.Find(BottomCel(SecHeadCol).Value - TermOfProjections, _
SectionHeadsRange.Cells(SectionHeadsRange.Cells.Count), , , xlPrevious)
If Found Is Nothing Then Set Found = Range(TopSecHeadAddress)
Set EarlistSectionhead = Found
End Function
Private Function DataDatesRange() As Variant
'Note that I have used hardcoded values in the Offset Function, these are
'called "Magic Numbers," because the next coder has no idea where they came from.
'You should replace them with Module Constants to make it easier to modify the code
'when you modify the sheet.
Set DataDatesRange = Range(EarliestSectionHead.Offset(1, 1), Range(BottomCell(DataDateCol)))
End Function
I would test these functions, but due to some Excel Bug, I would have to manually change all the Section Head Dates to the proper format. Step thru this sub using F8.
Sub Test()
Dim X
X = BottomCell(DataDateCol) 'Address String
X = SectionHeadsRange.Address
X = EarliestSectionHead.Address
X = DataDatesRange
End Sub
What do you think? Would it make better English to rename the BottomCell Function to BottomCellAddress, or, would it make better code to have it return a Range?
I have already changed the names of some functions and constants up to three times throughout the post, so as to make the code read more relevantly.
What are some good Constant names for the two Magic Numbers?
When we get those functions fixed, the rest of the code will be simple.
Reply With Quote
