Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 27

Thread: UserForm to Return the MAX date in a TextBox resulting from VLOOKUP in ComboBox

  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location

    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.

    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!
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,986
    Location
    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.
    VLookup won't do multiple matches

    Problem # 2 - If the VLOOKUP finds a match I want it to return the MAX date
    You can use Find...Findnext, but IMO, arrays are better in your situation
    Dim SchoolNames  As Variant
    Dim SchoolDays As Variant
    Dim i As long
    Dim MaxDate As Date
    Dim ThisName As String
    
    ThisName = SchoolNameComboBox
    
    With Worksheets("Future Appointments")
        SchoolNames = .Range(Intersect(.Range("A2").CurrentRegion, .Range("A:A")))
        SchoolDays =  .Range(Intersect(.Range("A2").CurrentRegion, .Range("F"F"))) 'Edit to fit
    End With
    
    For i = LBound(SchoolNames) + 1 to UBound(SchoolNames) '+1 to skip headers
       If SchoolNames(i) = ThisName Then MaxDate = Max(MaxDate, SchoolDays(i))
    Next i
    
    'If MaxDate = 0 then No Match found

    Problem # 2 - 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
    In Microsoft Office, there are differences between Days, Weeks and Months. So 7 days might really be 7 workdays, 4 weeks might not be one month. Google "Excel VBA DateAdd" for more info.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    Quote Originally Posted by SamT View Post

       If SchoolNames(i) = ThisName Then MaxDate = Max(MaxDate, SchoolDays(i))
    In Microsoft Office, there are differences between Days, Weeks and Months. So 7 days might really be 7 workdays, 4 weeks might not be one month. Google "Excel VBA DateAdd" for more info.
    Hi SamT,

    Thank you for your reply, much appreciated! I tried this code but got an error that said "Compile Error Sub of Function not defined" and the "Max" was highlighted in the quoted code above. Not sure what that means... Also, I understand the issues with days you mentioned. What I want to do is whatever date is returned, if there is a number in the other TextBox, take that number *7 and add that to the date returned. What this should do is give what the next appointment date would be after finding the future date already entered on the worksheet then adding the interval entered in weeks. I hope that makes sense.
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,615
    Max
    needs to be:
    Application.Max
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    Thank you p45cal, I have changed that but now I am getting a Run-time error '1004' that says "Application-defined or object-defined error" the portion of code is highlighted...

    SchoolNames = .Range(Intersect(.Range("A2").CurrentRegion, .Range("A:A")))
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,615
    reduce it to
    SchoolNames = Intersect(.Range("A2").CurrentRegion, .Range("A:A"))
    (and similar with the following line where also "F"F" should be "F:F").

    Also the following needs the red additions:
    If SchoolNames(i, 1) = ThisName Then MaxDate = Application.Max(MaxDate, SchoolDays(i, 1))
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    p45cal,

    I made the changes and now I am no loner getting the errors but it also is not pulling in any date in the ApptDateTextBox. I am posting all of the code I have so far. I have this entered in the UserForms SchoolNameComboBox_Change Event, is that correct?

    Private Sub SchoolNameComboBox_Change()
    
    
            Dim SchoolNames  As Variant
    
            Dim SchoolDays As Variant
            Dim i As Long
            Dim MaxDate As Date
            Dim ThisName As String
            
                ThisName = SchoolNameComboBox
                
                    With Worksheets("Future Appointments")
                        SchoolNames = Intersect(.Range("A2").CurrentRegion, .Range("A:A"))
                        SchoolDays = Intersect(.Range("F2").CurrentRegion, .Range("F:F"))  'Edit to fit
                    End With
                
                    For i = LBound(SchoolNames) + 1 To UBound(SchoolNames) '+1 to skip headers
                       If SchoolNames(i, 1) = ThisName Then MaxDate = Application.Max(MaxDate, SchoolDays(i, 1))
                    Next i
                
                'If MaxDate = 0 then No Match found
    
    
    End Sub
    Thank you for your help!
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,615
    Finish it with the likes of:
    ApptDateTextBox.text = iif(MaxDate=0,"",MaxDate)

    (Untested)
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,986
    Location
    I made the changes and now I am no loner getting the errors but it also is not pulling in any date in the ApptDateTextBox.
    Arrgh!

    When you see what that code is doing, you will see the answer.

    What this should do is give what the next appointment date would be after finding the future date already entered on the worksheet then adding the interval entered in weeks.
    Dim NextAppDate As Date
    
    If TextBoxOther = "" Them
       NextAppDate = maxDate
    Else
       NextAppDate = DateAdd(MaxDate, ww, Clng(TextBoxOther)
    End If
    If you see what that code does, then you can find the hint, the typo, and the error in it by yourself. One each.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  10. #10
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    Quote Originally Posted by p45cal View Post
    Finish it with the likes of:
    ApptDateTextBox.text = iif(MaxDate=0,"",MaxDate)

    (Untested)

    THAT DID IT!!! Thank you very much! However I have no idea what the "IIF" is, that is the first time I have seen that. Either way, thank you for your help!
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  11. #11
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    Quote Originally Posted by SamT View Post
    Arrgh!

    When you see what that code is doing, you will see the answer.



    Dim NextAppDate As Date
    
    If TextBoxOther = "" Them
       NextAppDate = maxDate
    Else
       NextAppDate = DateAdd(MaxDate, ww, Clng(TextBoxOther)
    End If
    I tried this but entering a number in the other TextBox does not advance the date in ApptDateTextBox. What I want it to do is take the number that is entered in WeeklyIntervalTextBox and multiply it by 7 then add it to the date that was returned. By doing this, as an example, if 11/1/2019 is returned in ApptDateTextBox and "4" is entered in WeeklyIntervalTextBox then I want to take the "4" and multiply it by 7 (for days in the week, which would be 28) then add that 28 to the date returned in ApptDateTextBox which would give a final result of 11/29/2019. Also out of curiosity, what is the "ww" and "Clng"? Thank you for your help.

    NextAppDate = DateAdd(MaxDate, ww, Clng(weeklyintervaltextbox)
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,986
    Location
    ww= in DateAdd indicates to add weeks
    Clng converts the number as text, (the TextBox value,) into a numerical value

    I tried this but entering a number in the other TextBox does not advance the date in ApptDateTextBox.
    Of course not, there is nothing in that bit of code that references AppTextBox.

    "4" is entered in WeeklyIntervalTextBox then I want to take the "4" and multiply it by 7 (for days in the week, which would be 28)
    Do you want to add days or weeks to the appointment date? There is a difference.
    DateAdd(MaxDate, ww, Clng(WeeklyIntervalTextBox))
    Adds weeks

    DateAdd(MaxDate, d, Clng(WeeklyIntervalTextBox) * 7)
    Adds days
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  13. #13
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,625
    Count the brackets:

    NextAppDate = DateAdd(MaxDate, ww, Clng(TextBoxOther)

  14. #14
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,615
    Do check out the help on DateAdd (https://docs.microsoft.com/en-us/off...teadd-function ) and note especially the order in which the arguments should appear and the data type of each argument.
    You may find it easier to just add 7 times what's in the other TextBox to MaxDate.
    2019-10-21_103954.jpg

    What is the name of this other TextBox?
    Last edited by p45cal; 10-21-2019 at 03:16 AM. Reason: spelling
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #15
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    p45cal, I will certainly check out the help doc you sent me when I get home tonight. Thank you! I have found so many things on the web, I got confused with it. I am pretty new to InputBox and while I get Arrays in Excel, I am extremely new to arrays in VBA. But just like everything else that Vbaxers have taught me over the last 14 years, this too I will get! You especially have been very helpful over the years so thank you.

    Quote Originally Posted by p45cal View Post
    What is the name of this other TextBox?
    The other TextBox is Named WeeklyIntervalTextBox.

    Quote Originally Posted by p45cal View Post
    You may find it easier to just add 7 times what's in the other TextBox to MaxDate.
    That is exactly what I want to do because each appointment is going to be on the same day of the week so the number of weeks x the value entered in WeeklyIntervalTextBox would give me the next appointment, on the same day of the week but that number of weeks into the future.
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  16. #16
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    Quote Originally Posted by SamT View Post
    ww= in DateAdd indicates to add weeks
    Clng converts the number as text, (the TextBox value,) into a numerical value
    Thank you for that clarification.

    Quote Originally Posted by SamT View Post
    Of course not, there is nothing in that bit of code that references AppTextBox.
    I am guessing here but... that might help??? The date that is returned from the Array from the earlier post goes into ApptDateTextBox, the value for weeks to advance that date is entered in WeeklyIntervalTextBox. So when the lookup returns the futuremost date for the school (from earlier posts) that date is returned in ApptDateTextBox, however if there is a value entered in WeeklyIntervalTextBox, then I want that date to be advanced by (WeeklyIntervalTextBox * 7)


    Quote Originally Posted by SamT View Post
    Do you want to add days or weeks to the appointment date? There is a difference.
    I definitely want to add days rather than weeks, it is just easier for the user to enter the number of weeks and allow the code to take it from there.

    DateAdd(MaxDate, d, Clng(WeeklyIntervalTextBox) * 7)
    I will give this a shot once I get home from work today, thank you very much for your help.
    Last edited by infinity; 10-21-2019 at 08:00 AM. Reason: Added some detail to previous post
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  17. #17
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,615
    Add
    Dim Weeks As Double
    at the top then at the bottom:
    On Error Resume Next
    Weeks = CLng(WeeklyIntervalTextBox.Text)
    On Error GoTo 0
    ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate + Weeks * 7)
    (If you put a negative number in you'll get earlier dates.)
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  18. #18
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,986
    Location
    Thank P45Cal for catching these syntax errors.
    DateAdd("d", Clng(WeeklyIntervalTextBox) * 7, MaxDate)
    And
    DateAdd("ww", Clng(WeeklyIntervalTextBox), MaxDate)
    Will give the same result.

    I always expect the student to do their homework and find all the errrors I leeve in.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  19. #19
    VBAX Regular
    Joined
    Jun 2005
    Posts
    94
    Location
    OK, so this is what I have done and it still is not working... I have to apologize, I really am not trying to waste anyone's time but I simply do not understand what I am doing wrong, like I said earlier, I am pretty new to InputBox and very new to Arrays in VBA... what am I doing wrong???

    Private Sub SchoolNameComboBox_Change()
    
    
            Dim SchoolNames  As Variant
            Dim SchoolDays As Variant
            Dim i As Long
            Dim MaxDate As Date
            Dim ThisName As String
            Dim NextAppDate As Date
            Dim Weeks As Double
            
                Sheets("Future Appointments").Select
                    ActiveSheet.Unprotect Password:=""
                        ThisName = SchoolNameComboBox
                            With Worksheets("Future Appointments")
                                SchoolNames = Intersect(.Range("A2").CurrentRegion, .Range("A:A"))
                                SchoolDays = Intersect(.Range("A2").CurrentRegion, .Range("F:F"))  'Edit to fit
                            End With
                        
                            For i = LBound(SchoolNames) + 1 To UBound(SchoolNames) '+1 to skip headers
                               If SchoolNames(i, 1) = ThisName Then MaxDate = Application.Max(MaxDate, SchoolDays(i, 1))
                            Next i
                        ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate)
                            
                            If WeeklyIntervalTextBox = "" Then
                               NextAppDate = MaxDate
                            Else
                                NextAppDate = DateAdd(MaxDate, ww, CLng(WeeklyIntervalTextBox))
                            End If
                            
                    On Error Resume Next
                    Weeks = CLng(WeeklyIntervalTextBox.Text)
                    On Error GoTo 0
                    ApptDateTextBox.Text = IIf(MaxDate = 0, "", MaxDate + Weeks * 7)
                            
    End Sub
    SamT - I really do appreciate your help and I am very good at finding errrors that others leeve in grammar and spelling but with coding, I do not do this for a living and am completely self taught with A LOT of help from all of you at VBAExpress, I couldn't do the things with Excel that I do without everyones help so thank you.
    He who possesses correct knowledge, has within him the potential to discern and then act upon truth!

  20. #20
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,615
    I refer you to the first sentence of msg#14.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •