Find, then find Next and append into certain cells as you go...

[mystik5]

I am looking to search through a worksheet, find a specific variable that is set from a user input. Find the value, then write a value to the right of field where the entry is found. I also need to do this as a loop, so wherever X is found in that list, write a value to the right of the search result, then find the next one and repeat.

This is what i have so far:

Set src = Workbooks.Open("\\a networkpath\excelfilename.xlsx")
cells.find(What:=myvariable. After:=Range("A1"))
g = activecell.offset(0, -1).value
Activecell.offset(0, -1).value = g & vbNewLine & Now() & " Email sent on this date"
Actvecell.offset)0,22).value = myvariable

So if i run this code it will find the search term once, since there is no loop and append as needed. It is just getting this to loop that i am having an issue. any help would be truly appreciated. I know this has to be an easy solution, however all the examples i have tried have not worked. thanks in advance for any and all help.

 

[DacEasy]

Hi, I would like to try this, can you demonstrate what you said with about 10 rows of sample data so I can see it.

 

[mystik5]

i have attached some sample data as requested. What I would like to do is to take input on the bank name, in this case we will use the example of Bank Fun. I would like to search the entire file, find the first entry of Bank Fun, go to the left of it to append a comment to the existing comment, then go to the right and add a date of when a reach out occurred, which will also come from user input. Then loop through the rest of the file looking for Bank fun appending to the comments section and adding in the date. do this until no more Bank Fun's are found.

Thanks for your help!!

sample_data.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Phase	Date	Comments	Bank Name	Contact Name	Backup Contact	Email Address	Country	Region	AML Applicable	Date of Contact
2	1	1/8/2022	Test Comment1	Bank Fun	John Smith	Terry Handle	email@somewhere.com	USA	NAM	Y
3	2	1/16/2022	Test Comment2	Bank Toy	John Smith	Terry Handle	email@somewhere.com	USA	NAM	N
4	1	3/1/2022	Test Comment3	Bank Food	John Smith	Terry Handle	email@somewhere.com	Canada	NAM	N
5	3	3/3/2022	Test Comment4	Bank Fun	John Smith	Terry Handle	email@somewhere.com	Brazil	LATAM	Y
6	3	3/18/2022	Test Comment5	Bank Pizza	John Smith	Terry Handle	email@somewhere.com	Mexico	LATAM	N
7	4	4/1/2022	Test Comment6	Bank Yolo	John Smith	Terry Handle	email@somewhere.com	China	APAC	N
8	5	6/8/2022	Test Comment7	Bank Fun	John Smith	Terry Handle	email@somewhere.com	Japan	APAC	Y
9	6	8/1/2022	Test Comment8	Bank West	John Smith	Terry Handle	email@somewhere.com	Ireland	EMEA	Y
10	7	8/15/2022	Test Comment9	Bank Fun	John Smith	Terry Handle	email@somewhere.com	Chile	LATAM	Y
11
Sheet1

 

[DacEasy]

Ok, the below should give you the described results:

Option Explicit
Sub Show()

UserForm1.Show vbModeless

End Sub

Private Sub forBank_Search()
    Application.ErrorCheckingOptions.BackgroundChecking = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim txtBank As String, txtAppd As String, txtDate As Date
    Dim lastRow As Long, i As Long
         
    If IsDate(UserForm1.txtDate.Value) = False Then     'Checks to see if the date value is in a valid date format.
        MsgBox "Verify that the date is valid"          'You could remove these four lines and make txtDate
        Exit Sub                                        'a string if your regions Date format doesn't work.
    End If
    
    txtBank = UserForm1.txtBankName.Value
    txtAppd = UserForm1.txtAppendComment.Value
    txtDate = UserForm1.txtDate.Value
    
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row      'Find the lastRow
    
    For i = 2 To lastRow                                'Checks to see if the bank name exists and updates column C
        If Range("D" & i).Value = txtBank Then
            Range("C" & i).Value = Range("C" & i).Value & " - " & txtAppd & " - " & txtDate
        End If
    Next i

    Columns("C:C").EntireColumn.AutoFit
    
    Application.ErrorCheckingOptions.BackgroundChecking = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    UserForm1.Label4.Caption = "Process Complete"
    
End Sub

For the userform, I added 4 labels, 3 textboxes, and 3 commandbuttons. Below are the Names on the UserForm for the textboxes and buttons. If you change, you will need to change in the code. The labels captions can be anything.

Label1 - Bank Name
Label2 - Append Comment
Label3 - Date
Label4 - "---"

txtBankName, txtAppendComment, txtDate

btnAppend
btnClear
btnExit

Below is the code for the form:

Private Sub btnAppend_Click()
Application.Run "Module1.forBank_Search"
End Sub

Private Sub btnClear_Click()
    txtBankName = ""
    txtAppendComment = ""
    txtDate = ""
    Label4 = "---"
End Sub

Private Sub btnExit_Click()
    Unload Me
End Sub

 

[bebo021999]

Where to past the result?
suppose paste to range L11 and downwards:

Option Explicit
Sub test()
Dim lr&, k&, f, fAddr As String, Bname As String, arr(1 To 6500, 1 To 1)
lr = Cells(Rows.Count, "D").End(xlUp).row
Bname = InputBox("Choose bank name:")
    Set f = Range("D1:D" & lr).Find(What:=Bname, after:=Range("D1"))
        If Not f Is Nothing Then
            fAddr = f.Address
            k = k + 1
            arr(k, 1) = f.Offset(0, -1) & vbNewLine & f.Offset(0, -2) & " Email sent on this date"
            Do
               Set f = Range("D1:D" & lr).FindNext(f)
               If Not f Is Nothing Then
                    k = k + 1
                    arr(k, 1) = f.Offset(0, -1) & vbNewLine & f.Offset(0, -2) & " Email sent on this date"
                End If
             Loop Until f.Address = fAddr
        End If
   Range("L11").Resize(k - 1, 1).value = arr
End Sub