VBA Run-Time Error 13 - Mismatch???

[mcdanibc]

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

[offthelip]

One thing to try when you have got a problem that you can't solve is to try a totally different method of doing it. I would never ever use Vlookup in VBa it is a worksheet function that is geared to ranges on a worksheet and it is inflexible and slow when you try to use it in VBA. e.g you can't pick up columns to the left of the matched column
A much better way of doing the same thing is to load the range which you want to do the lookup on into a variant array (ie into memory) and then do the search by looping through with a vba loop.
In your case because you are doing 5 separate lookups just to get different column numbers it is horrendously inefficient. In this case it is not a problem because you only do it once, but if this was in a loop of 10000 then you really would notice the difference in speed between using a variant array and using vlookup
So I have recoded (but not tested) the 5 lines where you are getting the error. I can assure with my code you will not get the same error ( you wil probably get a different error but hopefully you might solve that one)
At least i will have shown you an alternative and faster way of writing the code:

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)


' load the loop up range in to a variant array
lookuparr = Sheet2.Range("Lookup")
' loop through the array
For i = 1 To UBound(lookuparr)
' compare the first column of the array with the passnum
 If lookuparr(i, 1) = FRMLookup.passNum Then
 ' match found so copy the variables
.PassRem = lookuparr(i, 7)
.FirstName = lookuparr(i, 3)
.LastName = lookuparr(i, 2)
.DateIssued = lookuparr(i, 4)
.Notes = lookuparr(i, 8)
' match foudn so exit loop
 Exit For
 End If
Next i
End With