URGENT PLZ vlookup vba

[bensonsearch]

OK so I have decied to add into another sheet data i need, the below code works in other projects I have had but not in this one. it keeps on erroring and the data is there

Dim account As String
Dim check As Integer
Dim name As Variant
Dim state As Variant
Dim region As Variant
Dim count As Long
count = Sheets("Sheet4").Range("A2").CurrentRegion.Rows.count
account = TextBox2.Text
check = Len(account)
If check = 7 Then
name = Application.VLookup(account, Worksheets("Sheet4").Range("A1:N" & count), 2, False)
If IsError(name) Then
MsgBox "error"
Exit Sub
End If
state = Application.VLookup(account, Worksheets("Sheet4").Range("A1:N" & count), 10, False)
If IsError(state) Then
MsgBox "error"
Exit Sub
End If
region = Application.VLookup(account, Worksheets("Sheet4").Range("A1:N" & count), 14, False)
If IsError(region) Then
MsgBox "error"
Exit Sub
End If
'fill fields
Label5.Caption = name
Label9.Caption = state
Label10.Caption = region
Else
'nothing
End If

the vlookup is what i need help with

 

[xenou]

Personally I would put real vlookup formulas in a worksheet. Not an answer to your question - I have found such lookup formulas to work in vba so there's nothing wrong with the formulas as such (see my first post in the other thread). You will have to give the precise line the error occurs on, and what the values are that are involved - the lookup values, the lookup table, and any variables.