Reputation: 43
I created a tiny user-form where TextBox1 is txtsr and TextBox2 is txtdate. and the fourth column of my Vlookup table are Date Column, all cells are filled with date (dd-mm-yyyy) format.
With below code when I type an text in txtsr field, the txtdate is filled with date serial number instead of Date format.
Private Sub txtsr_Change()
Dim myRange As Range
Set myRange = Worksheets("VEHICLE IN").Range("B2:F20")
txtdate.Value = Application.WorksheetFunction.VLookup(txtsr, myRange, 4, False)
If Err.Number <> 0 Then txtdate.Value = ""
End Sub
how to show txtdate filled with correct date format (dd-mm-yyyy) instead of sate serial number?
Upvotes: 0
Views: 243
Reputation: 14383
This ought to do the job (untested - please try).
Private Sub txtsr_Change()
Dim myRange As Range
Dim MyDate As Variant
Set myRange = Worksheets("VEHICLE IN").Range("B2:F20")
MyDate = Application.WorksheetFunction.VLookup(txtsr, myRange, 4, False)
If Err.Number Then
txtdate.Value = ""
Else
txtdate.Value = Format(MyDate, "dd-mm-yy")
End If
End Sub
Upvotes: 1