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

 

[AC PORTA VIA]

Try this

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
Stop
End Sub

 

[DanteAmor]

Hi @JGM705. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

ws.Cells(i, "58").Value = foundCell.Value '

Just so that you take it into account for the future, in this case the 58 should be without quotes.

But try this macro:

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"
  Set ws = Sheets("Sheet1")
  Set r = ws.Range("U:AZ")
  Set f = r.Find(searchValue, , xlValues, xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      ws.Range("BA" & f.Row).Value = searchValue
      Set f = r.FindNext(f)
    Loop While f.Address <> cell
  End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[Peter_SSs]

@JGM705
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[JGM705]

Hi @JGM705. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.


Just so that you take it into account for the future, in this case the 58 should be without quotes.

But try this macro:

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"
  Set ws = Sheets("Sheet1")
  Set r = ws.Range("U:AZ")
  Set f = r.Find(searchValue, , xlValues, xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      ws.Range("BA" & f.Row).Value = searchValue
      Set f = r.FindNext(f)
    Loop While f.Address <> cell
  End If
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Thank you Dante, this is very close to what I'm trying to accomplish. I should have clarified the cells in th U:AZ range will include more than just LNR, such as LNR 0 1236 or LNR 5 4329. So I was searching on just LNR but wanted to capture the full value of the cell and paste to column BA. It appears your code ends up pasting the searchvalue and not the cell value.

 

[JGM705]

@JGM705
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Thank you Peter, the trials and tribulations of a newbie

 

[JGM705]

Try this

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
Stop
End Sub

Thanks for the response AC. I had to remove the STOP after the Next i. However, it does not appear to be copying the cell value just pasting the search value. I should have been clearer about the paste into column BA. The cell value will be more than just LNR like LNR 5 1235 or LNR 0 1222222. I figured it would be easier to search for LNR since its common, then copy the full cell value to BA

 

[JGM705]

Thank you Dante, this is very close to what I'm trying to accomplish. I should have clarified the cells in th U:AZ range will include more than just LNR, such as LNR 0 1236 or LNR 5 4329. So I was searching on just LNR but wanted to capture the full value of the cell and paste to column BA. It appears your code ends up pasting the searchvalue and not the cell value.

Please disregard. I changed the search value to LNR* and that appears to produce the desired results

 

[DanteAmor]

Hi @JGM705

The cell value will be more than just LNR like LNR 5 1235 or LNR 0 1222222.

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.

 

[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.

My apologies for not clearly stating the cell value would contain more than just the search value. I appreciate you help and thank you again.