I have a possible solution for you.
I copied your original example data into a spreadsheet called SegReplace.xls wrote some code and did some testing. Your original historical_e.xls did not have sufficient data to complete the testing, so I used the Book1.xls data. I made a copy called HistoricalDB.xls and tweaked a few entries to ensure that all three cases you mentioned were tested. HistoricalDB.xls is ~ 2x the size of your original because I made a copy of the original "Historical" sheet. I also added another test line in SegReplace.xls to test the 3rd case, i.e., NA in Historical and not enough data around the hole to use the 3pt moving average. My solution moves down the rows and across the cols. Therefore, the only time you will likely need the "last resort" is when you are trying to fill a hold in the first 1, 2 or 3 cols and the HistoricalDB is NA
In the attached zip file are SegReplace.xls and HistoricalDB.xls. Put them in the same directory, open SegReplace.xls and click on the Yellow button.
A few odds and ends:
- the yellow button's click routine actually calls a procedure called SearchAndReplace in code Module1.
- In the code module called MWE_Procs are a few functions used in SearchAndReplace. SearchAndReplace will not work without those routines
- You will notice that the value found for each of the 3 solution methods has a different color. You can change these colors by changing the values of ColorHistDB, Color3Pt and ColorLE in the SearchAndReplace macro. Present value are:
ColorHistDb = 3
Color3Pt = 5
ColorLE = 7
- the logic flow is documented reasonably well. I used descriptive names for the variables
- the code is "brute force" and could be made more elgant and more efficient, but that might also make it more difficult for you to understand.
- the variable named MsgBoxFlag is presently set to 0. Set it to 1 if you wish the macro to spit out status as it finds and fills holes.
I hope that this solves a large portion of your problem. If this works, please toggle this thread to solved. If not, let me know.
I suspect that you received no earlier replies because this problem is more complicated than a few lines (even a few dozen lines) of VBA code. It is not "hard", just time consuming. You owe the forum 2 dozen donuts and a gallon of Starbuck's best.