+ Reply to Thread
Results 1 to 4 of 4

Using Text box for Find / Find Next using VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    09-09-2019
    Location
    Lafayette, Louisiana
    MS-Off Ver
    2016
    Posts
    2

    Using Text box for Find / Find Next using VBA

    I am trying to use a textbox for a Click button to "find Next" whatever is typed in the adjacent search box. I was able to make the button work but it goes through the whole loop. I cant figure out how to just find one match and stop and not look for the next until the find next button is clicked. heres my code
    Sub Searchtoollist()
    
        Dim SearchRange As Range
        Dim Itemcell As Range
        Dim ItemName As String
        Dim FirstItemCell As String
        
        
        ItemName = Range("D11")
        
        Set SearchRange = Range("C15:E15", Range("C14:E14").End(xlDown))
        
        Set Itemcell = SearchRange.find(what:=ItemName, MatchCase:=False, LookAt:=xlPart)
        
        If Itemcell Is Nothing Then
            MsgBox "Not found"
        Else
            FirstItemCell = Itemcell.Address
            
            Do
                Itemcell.Select
                
                Set Itemcell = SearchRange.FindNext(Itemcell)
            Loop While Itemcell.Address <> FirstItemCell
                    
        End If
        
    End Sub
    Attached Files Attached Files
    Last edited by Leith Ross; 09-09-2019 at 07:13 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Using Text box for Find / Find Next using VBA

    For the time being, I will try like this :
    Sub Searchtoollist()
    
        Dim SearchRange As Range
        Dim Itemcell As Range
        Dim ItemName As String
        Dim FirstItemCell As String
        
        
        ItemName = Range("D11")
        If ActiveCell.Column <> 3 Then Range("C15").Select
        
        Set SearchRange = Range("C15:E15", Range("C14:E14").End(xlDown))
        Set Itemcell = SearchRange.find(what:=ItemName, MatchCase:=False, LookAt:=xlPart, After:=ActiveCell)
        
        If Itemcell Is Nothing Then
        MsgBox "Not found"
        Else
        Itemcell.Select       
        End If
        
    End Sub

  3. #3
    Registered User
    Join Date
    09-09-2019
    Location
    Lafayette, Louisiana
    MS-Off Ver
    2016
    Posts
    2

    Re: Using Text box for Find / Find Next using VBA

    That worked... thank you so much

  4. #4
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Using Text box for Find / Find Next using VBA

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 9
    Last Post: 12-21-2014, 06:29 PM
  2. VBA Find stopped working on Find Text (xlValue vs xlFormula)
    By ker9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-11-2014, 10:31 AM
  3. [SOLVED] To Find every instance of TEXT copy, repeat until no Find found
    By DadaaP in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-25-2013, 02:15 PM
  4. Find text in vertical colum and go across to find value
    By computergeek in forum Excel General
    Replies: 1
    Last Post: 06-06-2013, 10:31 PM
  5. [SOLVED] Trying to find a formula to find a random $ amount in a text in a cell
    By Miki1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2013, 10:13 PM
  6. [SOLVED] Set reference, find it, find where text matches, bring across value
    By gwsampso in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 10-28-2012, 10:59 PM
  7. Replies: 4
    Last Post: 09-18-2012, 10:54 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1