VBA to find the closest meeting date and return the date and meeting values into textboxes

[stirlingmw]

I am trying to get my userform to populate with data from a worksheet "Project Master" based on a Combobox selection "CmbFindProject". The form populates fine apart from 1 set of data. This data is a list of Meeting types and dates. There is currently 1 Combobox "ComboBox1" and 2 textboxes "TxtUpDat" and "TxtStake". when the userform is open and a project is selected, i can select a meeting type from the list in Combobox1, this then populates the other 2 textboxes. What i would like to do though is for the code to find the closest date to today (not past) and show this date, the Meeting Type (in Combobox1) and the stakeholders associated with that meeting in TxtStake.
The columns in the Project Master that contain the data are -
BE meeting type 1 date
BF Meeting type 1 Stakeholders
BG meeting type 2 date
BH Meeting type 2 Stakeholders
BI meeting type 3 date
BJ Meeting type 3 Stakeholders
and a sample of the code that runs when a selection is made from CmbFindProject is

totRows = Worksheets("Project Master").Range("A1").CurrentRegion.Rows.count
For i = 2 To totRows
    If Trim(Worksheets("Project Master").Cells(i, 1)) = Trim(CmbFindProject.text) Then
TxtProject.text = Worksheets("Project Master").Cells(i, 1).Value
CmbTeam.text = Worksheets("Project Master").Cells(i, 2).Value
etc..

the cells which return the desired data are
57, 58, 59, 60, 61 and 62

Thanks

Steve

 

[baitmaster]

A couple of things to think about

Depending where you are, working with dates in VBA can result in problems where VBA swaps between UK and US date formats. I'm in the UK and to avoid these problems I ALWAYS convert to long data type before processing - comparing the underlying number prevents accidental swapping of days and months. It also helps simplify the problem mentally as you're now just comparing numbers

Obviously you'll need a test to ensure the text box value can be converted to a long/date. Maybe use CLng(text string) and see if its between say 40k & 50k?

Have you tried using worksheetfunction.minifs to find the minimum value that's bigger than your start point? MINIFS formula is a recent addition so you may not have it in your version of Excel, but you could create an alternative array formula to evaluate the same thing

HTH