[Excel2007][VBA] Vlookup geeft fout 1004 bij missende waarde

[DK]

Ik wil een VLOOKUP functie toepassen in VBA (Excel 2007). Dit werkt prima zolang de gezochte waarde wordt gevonden in de referentietabel. Wanneer de waarde echter niet wordt gevonden krijg ik een foutmelding: "Fout 1004: Eigenschap Vlookup van klasse WorksheetFunction kan niet worden opgehaald."

Op zich logisch. Excel zal "#N/A" teruggeven, maar VBA staat dit niet toe en geeft een foutmelding.

Ik heb op internet gezocht en daar worden 2 oplossingen aangedragen om met deze foutmelding om te gaan.
Gecombineerd met mijn code ziet dat er als volgt uit:

code:

Sub Contract_Ophalen
     On Error Resume Next

     Contract_Type = Application.WorksheetFunction.VLookup(Val(Contract_Num), Worksheets("Contract").Range("E2:K1000"), 7, False)
     If IsError(Contract_Type) Then MsgBox "Not found"
End Sub

of:

code:

Sub Contract_Ophalen
     On Error Resume Next

     Contract_Type = Application.WorksheetFunction.VLookup(Val(Contract_Num), Worksheets("Contract").Range("E2:K1000"), 7, False)
     If Err.Number <> 0 Then MsgBox "Not found"
End Sub

Helaas werken beide oplossingen niet voor mij.
(Ook niet wanneer ik in de 1e code regels 4 en 5 combineer; dus: If iserror((Vlookup) then 'MsgBox' else 'Vlookup'))

Hoe los ik dit op?

[pedorus]

Wat werkt er precies niet aan de tweede oplossing?

[DK]

pedorus schreef op dinsdag 23 november 2010 @ 17:33:
Wat werkt er precies niet aan de tweede oplossing?

Dan krijg ik dezelfde foutmelding (Indien 'Contract_Num' niet wordt gevonden):
"Fout 1004: Eigenschap Vlookup van klasse WorksheetFunction kan niet worden opgehaald."

[pedorus]

Ga eens in de VBA-editor naar Tools->Options->Tabblad General->Error Trapping en kies dan voor Break on unhandled errors. Oh, en dit moet je natuurlijk even vertalen

[DK]

Het vertalen is gelukt!!

En daarmee is mijn probleem ook opgelost. Waarvoor dank!

Nog een laatste vraagje: Is het ook mogelijk deze optie-setting via de VBA-code te beinvloeden?
Ik wil namelijk mijn programma delen met anderen en, ivm gebruiksgemak, liever geen 'vereisten voor gebruik' meeleveren.

[Verwijderd]

deze instelling kan in excel niet via vba gewijzigd worden (wel in access bv), maar normaal gesproken moet er niets aangepast worden bij de gebruikers want de "break on all unhandled errors" is de standaard waarde.

edit : met de sendkeys methode is het wel mogelijk, maar ik raad het niet aan : voor nederlandstalige excel

Visual Basic:

Application.SendKeys "%{F11}%XO+{TAB}{RIGHT 2}%F~%{F4}"

[ Voor 29% gewijzigd door Verwijderd op 24-11-2010 17:25 ]

[pedorus]

Het schijnt ook via het registry te kunnen.

Ik denk echter dat je het beste de boel kan herschrijven zonder mogelijkheden tot fouten:

Visual Basic:

Sub Contract_Ophalen2()
     Contract_Type = Evaluate("VLookup(" & Val(Contract_Num) & _
         ", Contract!E2:K1000, 7, False)")
     If IsError(Contract_Type) Then MsgBox "Not found"
End Sub

Of iets als:

Visual Basic:

Sub Contract_Ophalen3()
     Set Contract_Type = Worksheets("Contract").Range("E2:E1000") _
         .Find(Val(Contract_Num), lookat:=xlWhole, lookin:=xlValues)
     If Contract_Type Is Nothing Then
        MsgBox "Not found"
        Exit Sub
     End If
     Set Contract_Type = Contract_Type.Offset(0, 6)
End Sub