

March 21, 2022




November 8, 2013

Hi Barry,
2 things you need to change:
Set rng = Sheet1.Columns("B:B").Find(What:=dDate, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Replace xlFormulas with xlValues, you will never find a date in a formula string that looks like: =DATE(2018,3,7)
Format B8:B38 as Short Date
Next, you can handle errors in a different way, just check if Rng is nothing, this means there was no result found for that date, you don't need to disable errors with On Error GoTo.
Set rng = Sheet1.Columns("B:B").Find(What:=dDate, _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
Sheet1.Cells(iCount, 11).Value = "Date not found: " & Format(dDate, "dd/mm/yyyy")
Else
rownumber = rng.Row
Sheet1.Cells(iCount, 11).Value = Sheet1.Cells(rownumber, 4).Value
End If
Set rng = Nothing
Next iCount


March 21, 2022

Hi Catalin.
Thank you for responding to my query. I had tried "xlValues" but I had not used any other date format.
However, having made the changes you suggest (I did actually copy/paste to avoid errors) I still get "Date not found: 30/12/1899"; i.e. the error rather than the matching 'ITEM' as the result in column K.
Just to confirm -- I am using Excel 2016.
Where am I going wrong?
Thanks again in anticipation

Answers Post


November 8, 2013

That date is not a valid date. Excel date system starts from 1/1/1900, this is Day 1. Today's date (april 11 2022) is represented by a number: 44662, meaning that 44662 days passed since day 1. The subject is complex, there is another system: In the 1904 date system, dates are calculated by using January 1, 1904, as a starting point.
You cannot use in excel dates before 1900 in numeric/date format, only as text.


March 21, 2022

Hi All -- Beepee again.
The first reply to this post works and solved my immediate problem. Many thanks to Catalin.
However I am still struggling to solve the issue of the search date not existing in the data list. Under these circumstances I would like to get the nearest later (or sometimes earlier?) date.
Any help much appreciated. Thank you


March 21, 2022

Hi. Yes...
The gardening tasks - - are listed in chronilogical order. Not all dates exist on the data sheet and of those that do, most are repeated several times (The search could be looking for a non existent date). See below sample worksheet. The search date is derived from a fairly simple function that adds nine days to the current date, (changes year to 2017-18) to look for outstanding jobs for the next 'week'.
Using the data below, and assume 'today' is 10 March then I would want to find either the first entry for the 19 March or the nearest later date (20 Mar in list). Hope this is sufficient. Thanks as always.
01/03/2018 | F | RED CURRANT |
07/03/2018 | F | CORNFLOWER |
07/03/2018 | F | GAZANIA |
07/03/2018 | F | RUDBECKIA |
07/03/2018 | H | CRESS |
07/03/2018 | M | LIME |
07/03/2018 | V | BROAD BEAN |
07/03/2018 | V | LEEK |
07/03/2018 | V | LETTUCE |
07/03/2018 | V | POTATO |
07/03/2018 | V | TOMATO |
10/03/2018 | H | MUSTARD |
10/03/2018 | V | CARROT |
10/03/2018 | V | RUNNER BEAN |
15/03/2018 | F | CHRYSANTHEMUM |
15/03/2018 | F | ESCHSCHOLTZIA |
15/03/2018 | F | LOBELIA |
15/03/2018 | F | PETUNIA |
15/03/2018 | F | VERBENA |
15/03/2018 | V | BROCCOLI |
15/03/2018 | V | BRUSSELS SPROUTS |
15/03/2018 | V | CUCUMBER |
18/03/2018 | V | SHALLOT |
18/03/2018 | V | TOMATO |
20/03/2018 | V | RUNNER BEAN |
21/03/2018 | V | POTATO |
23/03/2018 | F | MARIGOLD |
23/03/2018 | F | WATER HYSSOP |
23/03/2018 | H | BASIL |
23/03/2018 | H | CHERVIL |
23/03/2018 | H | ROCKET |


November 8, 2013

Is there a reason why you need vb code for this?
The problems you indicate come from poor data structure, instead of those 12+sheets and forms to add data, you should reorganize into a proper structure.
You can do a search similar to the vb search using a simple formula, this for example returns the row index of the match found:
=MATCH(J2,B:B,0)
If you want to find the next entry in case of an error, just use:
=IFERROR(MATCH(J2,B:B,0),MATCH(J2,B:B,1))


March 21, 2022

Hi Catalin, and thank you for the quick response. The 12 data sheets, and therefore the structure of the data, evolved over several years. I started out with a fairly straight forward log of purchases and sowing, planting, harvesting dates. I now have cultivation notes, weather data, seed saving, germination data etc. I am not sure I would know a proper structure -- Excel, macros, and more recently VBA have been pretty much self-taught (with help from Mr Google) and help from people like yourself which has been very much appreciated.
Anyway, I have a cell on the output form that displays the '9_day' date that I am searching for; so I am sure I can use your =MATCH... formula with the =IFERROR...; in place of what I have to find the necessary date.
So once again I am most grateful for your help. Thanks
1 Guest(s)
