VBA coding help

Solby

New Member
Joined
Aug 21, 2019
Messages
11
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
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,970
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?
 

mse330

Active Member
Joined
Oct 18, 2007
Messages
449
Welcome to Mr. Excel Solby !

You can place your code in code tags to make it easier to read by using
Rich (BB code):
Rich (BB 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


	
	
	
	


Rich (BB 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
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,970
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:

Solby

New Member
Joined
Aug 21, 2019
Messages
11
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?
 

Solby

New Member
Joined
Aug 21, 2019
Messages
11
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.
 

mse330

Active Member
Joined
Oct 18, 2007
Messages
449
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 ?
 

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
1,970
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?
 

Solby

New Member
Joined
Aug 21, 2019
Messages
11
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.
 

Forum statistics

Threads
1,085,097
Messages
5,381,694
Members
401,751
Latest member
bschiebe

Some videos you may like

This Week's Hot Topics

Top