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,039

    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, 2012, 2013



    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,039

    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, 2012, 2013



    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
    419

    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


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 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