alexdurc09
New Member
- Joined
- Sep 21, 2018
- Messages
- 17
Hello,
I have a simple user form where end users choose the unique identifier and the form returns a list of other related fields using VBA Vlookup coding. The code works fine the all other fields apart from wherethere is a date; for the date it returns the equivalent serial number for thedate held in the range. How can I make sure that the date is returned into theuser form using the vlookup?
The code is below.
Reg 7 / 12 / 15 / 16 /17 are the date fields. I am new toVBA coding so apologise if this isnt the most efficient way of doing a vlookup.
Private Sub Reg1_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.Reg1.Value) = 0 Then
MsgBox "This Container Number Doesn't Exist, Please Try Again..."
Me.Reg1.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.Reg2 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 2, 0)
.Reg3 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 3, 0)
.Reg4 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 4, 0)
.Reg5 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 5, 0)
.Reg6 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 6, 0)
.Reg7 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 7, 0)
.Reg8 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 8, 0)
.Reg9 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 9, 0)
.Reg10 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 10, 0)
.Reg11 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 11, 0)
.Reg12 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 12, 0)
.Reg13 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 13, 0)
.Reg14 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 14, 0)
.Reg15 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 15, 0)
.Reg16 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 16, 0)
.Reg17 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 17, 0)
End With
End Sub
Thanks,
Alex
I have a simple user form where end users choose the unique identifier and the form returns a list of other related fields using VBA Vlookup coding. The code works fine the all other fields apart from wherethere is a date; for the date it returns the equivalent serial number for thedate held in the range. How can I make sure that the date is returned into theuser form using the vlookup?
The code is below.
Reg 7 / 12 / 15 / 16 /17 are the date fields. I am new toVBA coding so apologise if this isnt the most efficient way of doing a vlookup.
Private Sub Reg1_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.Reg1.Value) = 0 Then
MsgBox "This Container Number Doesn't Exist, Please Try Again..."
Me.Reg1.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
.Reg2 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 2, 0)
.Reg3 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 3, 0)
.Reg4 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 4, 0)
.Reg5 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 5, 0)
.Reg6 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 6, 0)
.Reg7 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 7, 0)
.Reg8 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 8, 0)
.Reg9 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 9, 0)
.Reg10 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 10, 0)
.Reg11 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 11, 0)
.Reg12 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 12, 0)
.Reg13 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 13, 0)
.Reg14 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 14, 0)
.Reg15 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 15, 0)
.Reg16 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 16, 0)
.Reg17 = Application.WorksheetFunction.VLookup(Me.Reg1, Sheet1.Range("Lookup"), 17, 0)
End With
End Sub
Thanks,
Alex