Auto Populating a Date Text box with the latest date depending on the combo box selection

[KTSARA]

Hello Experts,
I am quite novice to VBA and still learning. So, any help from you would be highly appreciated.

I have an excel sheet (Sheet 4) with Machine codes (Column A) and Service Dates (Column B). Same machine code can be repeated in column A, but always with a unique service date. In my user form there is a combo box called MachineCodeComboBox and a text box called LastServiceDateTextBox.
I want to search for the machine code selected by the user from Sheet 4 and automatically fill the LastServiceDateTextBox with the latest service date of that particular machine code.

I used the below code to search for the machine code but, I couldn't figure out a way to insert the Max Function.

Private Sub MachineCodeComboBox_Change()
 With Me
        .LastServiceDateTextBox = Application.WorksheetFunction.VLookup(Me.MachineCodeComboBox, Sheet4.Range("A:B"), 2, False)
   End With
End Sub

Your expert opinions regarding this questions are highly appreciated.
Thanks a lot in advance

 

[Akuini]

Is it correct to assume that if we find the last entry of a particular Machine code then it has the last date in its adjacent cell (in col B)?

 

[KTSARA]

Is it correct to assume that if we find the last entry of a particular Machine code then it has the last date in its adjacent cell (in col B)?

Hello,
Thank you very much for taking your time to read and reply my question. Yes, last entry of the machine code would have its latest service date.

 

[Akuini]

Ok, try this:
But this is untested, so if it doesn't work then please upload your workbook (without sensitive data) to a free site (such as dropbox.com or google drive), then put the link here.

Private Sub MachineCodeComboBox_Change()
     Dim c As Range
     If Me.MachineCodeComboBox.Value <> "" Then
        Set c = Sheet4.Range("A:A").Find(What:=Me.MachineCodeComboBox.Value, LookIn:=xlValues, _
        lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
           Me.LastServiceDateTextBox = c.Offset(, 1).Value
        Else
           Me.LastServiceDateTextBox = ""
        End If
     End If
End Sub

 

[KTSARA]

Ok, try this:
But this is untested, so if it doesn't work then please upload your workbook (without sensitive data) to a free site (such as dropbox.com or google drive), then put the link here.

Private Sub MachineCodeComboBox_Change()
     Dim c As Range
     If Me.MachineCodeComboBox.Value <> "" Then
        Set c = Sheet4.Range("A:A").Find(What:=Me.MachineCodeComboBox.Value, LookIn:=xlValues, _
        lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
           Me.LastServiceDateTextBox = c.Offset(, 1).Value
        Else
           Me.LastServiceDateTextBox = ""
        End If
     End If
End Sub

Hello Akuini,
This works perfectly. Thank you so much for taking your valuable time to answer my question very quickly. You are indeed a genius.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.