XL 2010 VBA EXCEL FONCTION IF IS ERROR

[Aragon10]

Bonjour,

Je n'arrive pas à utiliser la fonction is error relative à la fonction VLOOKUP. un message d'erreur s'affiche toujours:

Private Sub CommandButton19_Click()
Dim x As Variant
x = Application.WorksheetFunction.VLookup(CDbl(TextBox31), Sheets("Feuil2").Range("Tablo"), 3, False)
If IsError(x) Then
Exit Sub
Else
TextBox32 = x
End Sub

Merci pour votre aide.

 

[Jean-Eric]

Bonsoir,
2 propositions à étudier.
Cdlt

Option Explicit

Private Sub CommandButton19_Click()
Dim x As Variant
    On Error Resume Next
    x = Application.WorksheetFunction.VLookup(TextBox31.Value, Sheets("Feuil2").Range("Tablo"), 3, False)
    If Err.Number = 0 Then
        TextBox32.Value = x
    Else
        Err.Clear
        Exit Sub
    End If
End Sub

Private Sub CommandButton19_Click()
Dim x As Variant
    x = Application.VLookup(TextBox31.Value, Sheets("Feuil2").Range("Tablo"), 3, False)
    If IsError(x) Then
        Exit Sub
    Else
        TextBox32.Value = x
    End If
End Sub

 

[job75]

Bonjour Aragon10, Jean-Eric,

Essayez :

Private Sub CommandButton19_Click()
Dim n As Variant, x As Variant
If IsNumeric(TextBox31) Then n = CDbl(TextBox31)
x = Application.VLookup(n, Sheets("Feuil2").Range("Tablo"), 3, False)
If Not IsError(x) Then TextBox32 = x 'Else TextBox32 = ""
End Sub

Bonne journée.