mcdanibc
10-24-2018, 09:04 AM
Hey! I am trying to create a user form that will lookup data from one sheet and then write to another. Everything works just fine, expect that I get a run time error each time I submit the form and then the form force ends. When I go to debug it, it lists the following line as the code error:
.PassRem = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 7, 0)
Here is the rest of the code for reference:
Private Sub PassNum_AfterUpdate()
If WorksheetFunction.CountIf(Sheet2.Range("A:A"), FRMLookUP.PassNum.Value) = 0 Then
MsgBox "Pass Number Unknown"
FRMLookUP.PassNum.Value = ""
Exit Sub
End If
With FRMLookUP
.PassRem = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 7, 0)
.FirstName = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 3, 0)
.LastName = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 2, 0)
.DateIssued = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 4, 0)
.Notes = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 8, 0)
End With
If PassRem.Value >= "25" Then
If MsgBox("Pass has been used, please reload.", vbQuestion + YesNO) <> vbYes Then
End If
End If
End Sub
Private Sub CommandButton1_Click()
ActiveCell = PassNum.Value
ActiveCell.Offset(0, 1) = PassUsed.Value
ActiveCell.Offset(1, 0).Select
Call resetform
End Sub
Sub resetform()
PassNum.Value = ""
PassRem.Value = ""
PassUsed.Value = ""
FirstName.Value = ""
LastName.Value = ""
DateIssued.Value = ""
Notes.Value = ""
FRMLookUP.PassNum.SetFocus
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
.PassRem = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 7, 0)
Here is the rest of the code for reference:
Private Sub PassNum_AfterUpdate()
If WorksheetFunction.CountIf(Sheet2.Range("A:A"), FRMLookUP.PassNum.Value) = 0 Then
MsgBox "Pass Number Unknown"
FRMLookUP.PassNum.Value = ""
Exit Sub
End If
With FRMLookUP
.PassRem = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 7, 0)
.FirstName = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 3, 0)
.LastName = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 2, 0)
.DateIssued = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 4, 0)
.Notes = Application.WorksheetFunction.VLookup(CLng(FRMLookUP.PassNum), Sheet2.Range("Lookup"), 8, 0)
End With
If PassRem.Value >= "25" Then
If MsgBox("Pass has been used, please reload.", vbQuestion + YesNO) <> vbYes Then
End If
End If
End Sub
Private Sub CommandButton1_Click()
ActiveCell = PassNum.Value
ActiveCell.Offset(0, 1) = PassUsed.Value
ActiveCell.Offset(1, 0).Select
Call resetform
End Sub
Sub resetform()
PassNum.Value = ""
PassRem.Value = ""
PassUsed.Value = ""
FirstName.Value = ""
LastName.Value = ""
DateIssued.Value = ""
Notes.Value = ""
FRMLookUP.PassNum.SetFocus
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub