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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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?
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top