Find, Copy,and Paste

[JGM705]

Hello All,

I'm in need of some assistance. I have a sheet that has data in Columns U through AZ. I am attempting to search each row to find specific text. Once that text is found in a cell, I want to copy that cell value and paste it in the same row but in column BA. I have hodgepodge various things together from various articles. But I seem to be getting nowhere. Any help would be greatly appreciated.

Sub SearchAndCopy

     Dim ws As Worksheet
     Dim searchValue As String
     Dim lastRow As Long
     Dim i As Long


     Set ws = Worksheets("Sheet1") ' Set the worksheet where you want to perform the search

     searchValue = "LNR"        ' Set the search value 
     lastRow = ws.Cells(ws.Rows.Count, "U").End(xlUp).Row    ' Find the last row with data in column U 
 
    For i = 2 To lastRow  ' Loop through each row
        If WorksheetFunction.CountIf(ws.Range("U" & i & ":AZ" & i), searchValue) > 0 Then 'Check if the search value is found in cells U2 to AZ1338
       Set foundCell = ws.Rows(i).Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False  ' Find the  cell with the search value in the row

            If Not foundCell Is Nothing Then
               ws.Cells(i, "58").Value = foundCell.Value     ' Copy the value to column BA in the same row

             End If
        End If
     Next i   
End Sub

 

[JGM705]

Hi @JGM705

It would be nice if you wrote these requirements from the original post, that way the solutions delivered will be more complete.

I'm glad to know you solved it.


---------------------
If the value to be searched is found anywhere in the cell, it can also be: "*LNR*"
Or also changing the search parameter to xlPart.

I also changed the result to f.Value so you get the actual value of the cell that contains "LNR"

Sub SearchAndCopy()
  Dim c As Range, r As Range, f As Range, cell As String
  Dim ws As Worksheet
  Dim searchValue As String

  searchValue = "LNR"                       'or "*LNR*" o "LNR*"
  Set ws = Sheets("Sheet1")
  Set r = ws.Range("U:AZ")
  Set f = r.Find(searchValue, , xlValues, xlPart)   'or xlwhole
  If Not f Is Nothing Then
    cell = f.Address
    Do
      ws.Range("BA" & f.Row).Value = f.Value
      Set f = r.FindNext(f)
    Loop While f.Address <> cell
  End If
End Sub

Im glad to help you, thanks for the feedback.

Hello Dante I hope you are well. I'm making my macro more cumbersome that it should by building out each searchvalue. I'm hoping you can further assist me. The searchValue is going to have 200+ possibilities. I have created a sheet called FeatDB with all the possibilities populated in column A and would like to use this as a means to provide the search value. Lets say LNR 00 1234, RAG, ABC and GIC AAA 01 are the first four items. The expected functionality would be to Find LNR*, copy the cell content of LNR 00 1234 to Column BA1, cycles through the U:AZ range as already configured. Once complete, then go to the next item RAG. Find RAG, copy cell and paste to BB1, etc. So as each different item is found, the B(a) column is incremented as well. Thank you in advance.

 

[DanteAmor]

Hi @JGM705 , I'm fine, thank you for asking, I also hope you are well.

Although what you are asking for has to do with the same information, the design of the macro is different.

Please create a new thread and there explain in great detail what you have on each sheet, give several examples and also what you expect as a result, also with several examples.