Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Vba - If Value Not Found

  1. #1
    Join Date
    28th June 2007
    Posts
    24

    Vba - If Value Not Found

    Hello,

    I am stuck on a simple find code.

    Basically, I want to enter a value in a textbox, search for it in an worksheet, and populate other textboxes with adjacent values if the value is found.

    Anyway, the problem is that if the value is not found, I get a debug error.

    Can someone help me with this code?

    VB:
    Dim test1 
    test1 = TextBox1.Value 
    Worksheets("data1").Activate 
    Find_Range(test1, Cells, xlFormulas, xlWhole).Select 
    TextBox2 = ActiveCell.Value 
    TextBox3 = ActiveCell.Offset(0, 1).Value 
    
    
    I'm sure there's far better code to do what I need, but I tried to keep it simple. With the above code, it only works if the value is found. If it's not, I get an error.

    So how do I make it so that if the value is not found, the value of the textbox2 is "Not Found" or something...

    Thanks for the help!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,062

    Re: Vba - If Value Not Found

    Hi

    Something along these lines?

    VB:
    Sub wigi() 
         
        Dim test1 As String 
        Dim FoundRange As Range 
         
        test1 = TextBox1.Value 
        Worksheets("data1").Activate 
         
        Set FoundRange = Sheets("data1").Cells.Find(what:=test1, LookIn:=xlFormulas, lookat:=xlWhole) 
         
        If FoundRange Is Nothing Then 
            TextBox2.Text = "not found" 
            Textbox3.Text = "not found" 
        Else 
            TextBox2.Text = FoundRange.Value 
            Textbox3.Text = FoundRange.Offset(0, 1).Value 
        End If 
    End Sub 
    
    
    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  3. #3
    Join Date
    28th June 2007
    Posts
    24

    Re: Vba - If Value Not Found

    NICE!!!

    Thank you sir . That's perfect.

    Just for that, I am even keeping the sub name.

    Have a great weekend!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,062

    Re: Vba - If Value Not Found

    NICE!!!

    Thank you sir . That's perfect.

    Just for that, I am even keeping the sub name.
    Hmm, again an instance where the sub name doesn't excel in originality...

    Glad it works, have a good weekend too.

    Wigi
    Regards,

    Wigi

    Excel MVP 2011-2014



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

  5. #5
    Join Date
    6th June 2014
    Posts
    1

    Re: Vba - If Value Not Found

    how to update the cells and save changes

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th November 2013
    Location
    UK
    Posts
    778

    Re: Vba - If Value Not Found

    Hi hugov, welcome to Ozgrid.

    If you have a question related to this issue, please start your own thread as per the forum rules that you agreed to upon joining.

    Regards

    Have-A-Read!
    ----------------------------------------------
    # Excel/VBA Golden Rules [by ​Dave Hawley]
    # Excel Best Practices
    # Forum Rules
    ----------------------------------------------
    Wilde XL Solutions
    Free VBA -> HTML web formatter
    Free VBA Code snippets
    Test your regular expressions*
    ​*uses PHP RegEx - some features not supported in VBScript.RegExp


    An SQL statement walks up to two tables in a bar and asks "May I join you?"

Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Possible Answers

  1. Macro Not Found
    By DBrodak in forum EXCEL HELP
    Replies: 3
    Last Post: April 28th, 2007, 05:39
  2. VBA Module not found
    By Chari in forum EXCEL HELP
    Replies: 12
    Last Post: November 20th, 2005, 12:46
  3. If Not found...
    By tostrowski in forum EXCEL HELP
    Replies: 1
    Last Post: May 16th, 2005, 22:53
  4. File Not Found
    By nojy in forum EXCEL HELP
    Replies: 2
    Last Post: April 18th, 2005, 20:27
  5. If File Not Found
    By Anakus04 in forum EXCEL HELP
    Replies: 3
    Last Post: February 11th, 2005, 09:17

Bookmarks

Posting Permissions

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