Results 1 to 7 of 7

Vba vlookp error

This is a discussion on Vba vlookp error within the Excel Questions forums, part of the Question Forums category; Dear all, i have facing problem in below mention codes these code are working properly if value has match but ...

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Location
    Faisalabad Pakistan
    Posts
    390

    Post Vba vlookp error

    Dear all,
    i have facing problem in below mention codes these code are working properly if value has match but when value not match its show run time error please suggest .

    TextBox5.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 5, 0)
    TextBox13.Text = WorksheetFunction.VLookup(TextBox5.Value, Sheets("Basis").Range("I1:J100"), 2, 0)
    TextBox15.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 2, 0)
    TextBox11.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 3, 0)
    TextBox12.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 4, 0)
    TextBox16.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:H60000"), 8, 0)
    TextBox17.Text = WorksheetFunction.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:H60000"), 6, 0)

  2. #2
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Vba vlookp error

    Try replacing WorksheetFunction with Application
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Location
    Faisalabad Pakistan
    Posts
    390

    Default Re: Vba vlookp error

    Sir found same problem

  4. #4
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Vba vlookp error

    Try like this

    Code:
    Dim x As Variant
    x = Application.VLookup(TextBox3.Value, Sheets("Basis").Range("A1:F60000"), 5, 0)
    If Not IsError(x) Then
        TextBox5.Text = x
    Else
        TextBox5.Text = "Not found"
    End If
    Also, take a look here Daily Dose of Excel Blog Archive The WorksheetFunction Method
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Vba vlookp error

    Also, if the textbox contains a number

    Code:
    Dim x As Variant
    x = Application.VLookup(Val(TextBox3.Value), Sheets("Basis").Range("A1:F60000"), 5, 0)
    If Not IsError(x) Then
        TextBox5.Text = x
    Else
        TextBox5.Text = "Not found"
    End If
    HTH, Peter
    Please test any code on a copy of your workbook.

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Location
    Faisalabad Pakistan
    Posts
    390

    Default Re: Vba vlookp error

    Can we use all this formula for combine code at once??

  7. #7
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    Default Re: Vba vlookp error

    You can re-use the variable x
    HTH, Peter
    Please test any code on a copy of your workbook.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com