Finding a date without a search

Hi,
I have a programme that each day inserts 30 forward dates’ data at the bottom of the previous days data.
My requirement now is that when an earlier date’s amended data is inserted at the bottom I need to find that date above and replace this previous data with the new data from below. If that date was not previously added I need to insert it in the correct date order.
The way I have done this so far, please see attached, works when replacing or inserting just the immediate date above, my problem now is how to continue searching above for previous dates or inserting a date if not previously used.
Each days date is highlighted with a red fill so as not to confuse too much with so many dates.
Allowing for the days date row and a day’s total row every day has 32 rows of data.
After inserting at the bottom the active cell is the days date.
With so many dates in column 'I' just searching for a dates doesn’t work, so I am trying to search for the date in every 32 rows above.
Any help would be very much appreciated.
I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)
Attachment 10438

Algorithm:

• Dim Found As Range
• For Each Cel in Date Column
• If Cel = "" , Exit Loop
• Set Found = DateColumn.Find(Cel).Resize(To data range width)
• If not found is nothing
• Cel = Found
• Found.Delete Shift upwards
• Next Cel

Hi SamT,
Thank you for your quick reply to my post.
Unfortunately I'm not advanced enough to understand your algorithm.
How would I use your code.

OK, I downloaded your example,

Let's talk about the first three days (red Cells)

On 4/6/2013, you want the data from rows (4/6/2013) 35 - 63 placed in rows (3/6/2013) 4 - 32.

Then on 5/6/2014, you want the data from (5/6/2013) Rows 67-95 Put in the date matching rows of the 3/6/2013 and the matching rows of 4/6/2013, making all three days' data identical for identical dates.

IOW each days section should have the updated data from all the following day's sections

At the end of the Month, the data in the first day's section should be historical, the data in the 2nd day of the month should be historical, except the lat row in the section will be predictive.

And so forth until today's section will only predictive values.

I noticed that even the predictive data changes from day to day. Do you want the past days' sections to reflect the change in predictive data? What if Predictive data changes to nothing form some value?

The only difference I see in the algorithm is that the Find must be in the Up direction from the bottom and the loop will end when Found is nothing.

I have attached an updated file to try to explain my request in better detail.
Attachment 10443
In Example1 the days data is from the 5th August in cell I1635 (The Active Cell) to the HKD total in cell O1666. I want this data to be cut and pasted into cells I1442 to O1473 therefore replacing and updating this data.

In Example2 the days data is from the 5th August in cell I1603 (The Active Cell) to the HKD total in cell O1634. I want this data to be cut and inserted at cell I1442 to O1473 therefore adding this data.

In both examples I am searching using the active cell, which will always vary, looking for the matching date to replace or insert if that date is missing.
I hope that is clearer

IS your post # 5 in response to my #4?

Hi SamT,
Thanks again for your help.
Yes the predictive data changes from day to day. If Predictive data changes to nothing then I want zero.
Did you see the code I have started with?
Sorry no my post 5 was sent before I saw post 4

You are using two different date formats and it is really confusing me. OK, I formatted all the dates to UK style and it makes more sense.

Each Red date holds data for the thirty days starting the next calendar date of the following month. ie; 1/1/01 section has dates from 2/2/01 to 2/2/01 + 30 days.

Or is it supposed to be each date holds thirty days starting in 31 days?

BTW, We only need to see three red dates to figure things out, so shorten the example data sheets

I'm sorry I thought I had corrected a previous problem with the formatting to all be the same.
Attachment 10445
The 30 days start from the days date every day.
I have shortened the file to just August as I think we do need a few dates to play with.

Hi SamT,
Just to say that I'm now going to close now as I have to be up early tomorrow morning. I will however go straight to this thread first thing for your comments.
Thanks again for your help.

Hi SamT.

I have been experimenting and if I separate the days date from the 30 days dates, by moving them to the left, I can use the Date2 code, but if the date is missing it doesn't work yet. please see attached.
Attachment 10453

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.

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.

Code:

---

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

Code:

---

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

---

Code:

---

[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

---

Code:

---

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.

Code:

---

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.

Hi SamT,
Wow that is an extremely detailed solution. Please give me some time to understand and work through it.
Thank you for your time and expertise.

Hi SamT,
I have studied your proposal as best I can but as I said in post 3 I'm not advanced enough to understand your coding. Please therefore do not spend any more of your valuable time on this project. I am continuing with the short and simple code I added in post 11 attachment Date (6) which works OK but have now added an alert message for any occasion where a date is missing.
Thank you again for your time and expertise.