Announcement

Collapse
No announcement yet.

Vba - If Value Not Found

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

    Code:
            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!

  • #2
    Re: Vba - If Value Not Found

    Hi

    Something along these lines?

    Code:
    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.com ==> English articles ==> Excel memes

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

    Comment


    • #3
      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!

      Comment


      • #4
        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.com ==> English articles ==> Excel memes

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

        Comment


        • #5
          Re: Vba - If Value Not Found

          how to update the cells and save changes

          Comment


          • #6
            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

            Comment

            Working...
            X