UserForm to Return the MAX date in a TextBox resulting from VLOOKUP in ComboBox
Hi VBaxers,
I have a UserForm that has a ComboBox and Several TextBoxes. One TextBox returns the date from a VLOOKUP of the entry in the ComboBox on the UserForm. This actually works great as far as I have gotten but I am having a few problems with expanding it.
Code:
Private Sub SchoolNameComboBox_Change()
Dim nextApptDate
nextApptDate = WorksheetFunction.VLookup(SchoolNameComboBox.Value, Worksheets("Future Appointments").Range("A2:J2500"), 6, False)
ApptDateTextBox.Value = nextApptDate
If Not IsDate(Me.ApptDateTextBox.Text) Then
Me.ApptDateTextBox.Text = Format(Me.ApptDateTextBox.Text, "m/d/yyyy")
End If
End Sub
Problem # 1 - Currently this returns the first result from the VLOOKUP (which is a date) but because there may be multiple entry's for the selected value in the ComboBox I need it to return the date that is most in the future.
Problem # 2 - If the VLOOKUP finds a match I want it to return the MAX date but I also have another TextBox on the UserForm that the user will enter a number, this number will represent a number of weeks to advance the date returned i.e. if the VLOOKUP returns 11/1/2019 and there is a value in the other TextBox of 4 then I want the returned value to be 11/1/2019 + 28 days (4 weeks) which would be a result of 11/28/2019.
Problem # 3 - If the user selects from the ComboBox and but the VLOOKUP cannot find a match I want the value in the ApptDateTextBox to be blank, currently it is giving an error.
Thank you so much for any help... you guys have taught me sooooo much over the years and I cannot articulate my appreciation enough!