Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: VBA coding help

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA coding help

    Hi all,

    Below is my coding that helps me search for a word/name in a certain column and return data that relates to this word/name. The values are returned in a neat userform. The only issue I have is that the text I search for may not be unique. There may be 2 or 3 other instances of this word/name. i.e I search for John Smith and it returns age, location etc etc. But my search will stop at the first John Smith found. Is there a piece of coding I can put in that will cycle through all the John Smiths and not just stop at the first instance found. Thanks in advance.

    Private Sub txtTAname_Change()


    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Charolais SNPs Parentage")




    wsLR = ws.Cells(Rows.Count, 18).End(xlUp).Row






    For x = 6 To wsLR


    If ws.Cells(x, 18) = Me.txtTAname Then

    Me.txtTAnumber = ws.Cells(x, "O")
    Me.txtTAstatus = ws.Cells(x, "V")
    Me.txtSname = ws.Cells(x, "AC")
    Me.txtSstatus = ws.Cells(x, "AF")
    Me.txtDname = ws.Cells(x, "X")
    Me.txtDstatus = ws.Cells(x, "AA")
    Me.txtTAid = ws.Cells(x, "N")


    Exit Sub

    End If


    Next x


    End Sub

  2. #2
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,895
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA coding help

    Hello & welcome,

    You would need to use the find method. I started writing the solution but you fail to explain what exactly to do if there is more than one instance of search value found

    Ie: Find one instance so fill text boxes on form, find another instance - do we overwrite the text boxes?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  3. #3
    Board Regular
    Join Date
    Oct 2007
    Location
    Kuwait
    Posts
    449
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA coding help

    Welcome to Mr. Excel Solby !

    You can place your code in code tags to make it easier to read by using [code] before your code and [/ code] after your code (without the space) For more details click the link in my signature

    Now to your question, you can keep looping until the end to find all occurrences of the search text by removing the "Exit Sub" line but I think you're copying the data into a user form so if you do, it will override the previous search results & will keep the last one only

    Code:
    Private Sub txtTAname_Change()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Charolais SNPs Parentage")
    wsLR = ws.Cells(Rows.Count, 18).End(xlUp).Row
    For x = 6 To wsLR
        If ws.Cells(x, 18) = Me.txtTAname Then
            Me.txtTAnumber = ws.Cells(x, "O")
            Me.txtTAstatus = ws.Cells(x, "V")
            Me.txtSname = ws.Cells(x, "AC")
            Me.txtSstatus = ws.Cells(x, "AF")
            Me.txtDname = ws.Cells(x, "X")
            Me.txtDstatus = ws.Cells(x, "AA")
            Me.txtTAid = ws.Cells(x, "N")
        Exit Sub ' <--- this line
        End If
    Next x
    End Sub

    Check the List of BB codes


  4. #4
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,895
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA coding help

    This code may explain it better. Read the comments and let me know what to do (or the code may point you in the right way of the solution)

    Code:
    Private Sub txtTAname_Change_New()
    
    
        Dim ws As Worksheet
        Dim wsLr As Long
        Dim rSearch As Range 'range of cells to search
        Dim rFind As Range 'Used in the find method
        Dim sFirst As String 'save the first address we find
        
        'set the worksheet variable
        Set ws = ThisWorkbook.Sheets("Charolais SNPs Parentage")
        
        'get last used row in column 18
        wsLr = ws.Cells(Rows.Count, 18).End(xlUp).Row
        
        'set the search range to be R6 to last used row in column R
        Set rSearch = ws.Range(ws.Cells(6, 18), ws.Cells(wsLr, 18))
        
        Set rFind = rSearch.Find(Me.txtTAname)
        
        'check we have a match
        If rFind Is Nothing Then
            'no match so cancel search and inform user
            'MsgBox Me.txtTAname & " not found in search range", vbInformation, "Not Found"
            Exit Sub
        End If
        
        'If we get here we have a match
        
        'Remember the first address of found cell
        sFirst = rFind.Address
        
        'fill in the text boxes
        Me.txtTAnumber = ws.Cells(rFind.Row, "O")
        Me.txtTAstatus = ws.Cells(rFind.Row, "V")
        Me.txtSname = ws.Cells(rFind.Row, "AC")
        Me.txtSstatus = ws.Cells(rFind.Row, "AF")
        Me.txtDname = ws.Cells(rFind.Row, "X")
        Me.txtDstatus = ws.Cells(rFind.Row, "AA")
        Me.txtTAid = ws.Cells(rFind.Row, "N")
        
        'look for next instance
        Set rFind = rSearch.FindNext(rFind)
        
        'loop until the address of the found cell is the same as the first
        Do Until rFind.Address = sFirst
            'If we get here another instance has been found
            'it is here I'm unsure what to do with the additional instances of the found value
            
            
            
            '*code to deal with additional instances goes here
            
            
            'find next instance
            Set rFind = rSearch.FindNext(rFind)
        Loop
    
    
    End Sub
    Last edited by gallen; Aug 21st, 2019 at 11:16 AM.

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA coding help

    Hi Gallen,

    Thanks for the welcome and the help.

    Yes, when I find another instance I want the userform text-boxes to fill out with the new information. Also, what do you think is the best way to look for a second instance occurring? Hit the enter button or make a button that will toggle through them?

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA coding help

    Quote Originally Posted by gallen View Post
    This code may explain it better. Read the comments and let me know what to do (or the code may point you in the right way of the solution)

    Code:
    Private Sub txtTAname_Change_New()
    
    
        Dim ws As Worksheet
        Dim wsLr As Long
        Dim rSearch As Range 'range of cells to search
        Dim rFind As Range 'Used in the find method
        Dim sFirst As String 'save the first address we find
        
        'set the worksheet variable
        Set ws = ThisWorkbook.Sheets("Charolais SNPs Parentage")
        
        'get last used row in column 18
        wsLr = ws.Cells(Rows.Count, 18).End(xlUp).Row
        
        'set the search range to be R6 to last used row in column R
        Set rSearch = ws.Range(ws.Cells(6, 18), ws.Cells(wsLr, 18))
        
        Set rFind = rSearch.Find(Me.txtTAname)
        
        'check we have a match
        If rFind Is Nothing Then
            'no match so cancel search and inform user
            'MsgBox Me.txtTAname & " not found in search range", vbInformation, "Not Found"
            Exit Sub
        End If
        
        'If we get here we have a match
        
        'Remember the first address of found cell
        sFirst = rFind.Address
        
        'fill in the text boxes
        Me.txtTAnumber = ws.Cells(rFind.Row, "O")
        Me.txtTAstatus = ws.Cells(rFind.Row, "V")
        Me.txtSname = ws.Cells(rFind.Row, "AC")
        Me.txtSstatus = ws.Cells(rFind.Row, "AF")
        Me.txtDname = ws.Cells(rFind.Row, "X")
        Me.txtDstatus = ws.Cells(rFind.Row, "AA")
        Me.txtTAid = ws.Cells(rFind.Row, "N")
        
        'look for next instance
        Set rFind = rSearch.FindNext(rFind)
        
        'loop until the address of the found cell is the same as the first
        Do Until rFind.Address = sFirst
            'If we get here another instance has been found
            'it is here I'm unsure what to do with the additional instances of the found value
            
            
            
            '*code to deal with additional instances goes here
            
            
            'find next instance
            Set rFind = rSearch.FindNext(rFind)
        Loop
    
    
    End Sub
    Thanks Gallen, code looks good. Like I mentioned in the previous post I would like the textboxes in the userform to fill out with the new information.

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Location
    Kuwait
    Posts
    449
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA coding help

    Quote Originally Posted by Solby View Post
    I would like the textboxes in the userform to fill out with the new information.
    Have you tried to remove the "Exit Sub" in your existing code as mentioned in post #3 ?

    Check the List of BB codes


  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA coding help

    Quote Originally Posted by mse330 View Post
    Have you tried to remove the "Exit Sub" in your existing code as mentioned in post #3 ?
    I have. Again, I think to get to the next instance I need to add a button

  9. #9
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,895
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA coding help

    Ahhh now I understand so you want the user to be able to decide whether to accept these details or move on to the next?

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  10. #10
    New Member
    Join Date
    Aug 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA coding help

    Quote Originally Posted by gallen View Post
    Ahhh now I understand so you want the user to be able to decide whether to accept these details or move on to the next?
    Correct. A little bit like the control "F" find function in excel I suppose.

Some videos you may like

User Tag List

Tags for this Thread

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
  •