Search for text in a column, copy the text in the same row different column, paste to different sheet

andymalan10 · Jun 12th 2022

Dear Learned Friends, i have searched and found similar request but not exact. Any help will greatly be appreciated.

I have certain text found in sheets "Edging" A1

I need to match the text with text found in sheets "Cabinets" column AC

when a match is found, copy the text in same row but column AI

Paste to sheets "Edging" B1

stop search.

Code I have so far cobbled together:

Code

Application.ScreenUpdating = False


       Dim lRow As Long
       Dim fValue As Range


lRow = Sheets("Cabinets").Range("AC" & Rows.Count).End(xlUp).Row


For Each cell In Sheets("Cabinets").Range("AC1:AC" & lRow)
         Set fValue = Sheets("Edging").Columns("C:C").Find(cell.Value)
           If fValue Is Nothing Then GoTo NextCell
             If cell.Offset(0, 1).Value = fValue.Offset(0, 3).Value Then
               cell.Offset(0, 6).Copy
                 Sheets("Edging").Range("E14").PasteSpecial xlPasteValues
                   End If
NextCell:
Next cell


Application.CutCopyMode = False
Application.ScreenUpdating = True


'
         
         
         
        End Sub

Display More

**KjBox** · Jun 12th 2022

Maybe

Code

Sub MatchText()
    Dim x, y, i&
    x = Sheets("Cabinets").Columns(29)
    y = Sheets("Cabinets").Columns(35)
    
    With Sheets("Edging")
        s = .Cells(1, 1)
        If Not IsError(Application.Match(.Cells(1, 1), x, 0)) Then
            i = Application.Match(.Cells(1, 1), x, 0)
            .Cells(1, 2) = y(i, 1)
        Else
            MsgBox "No match found", 64, "Matching Text"
        End If
    End With
    
End Sub

Display More

Search for text in a column, copy the text in the same row different column, paste to different sheet

Participate now!

Share

Similar Threads

VBA coding search box through multiple sheets

Changing file name and dates VBA macro

Command Button to paste data from specific Cells of Specific sheet to specific Cells of Specific sheet

VBA Code to copy value in a cell and paste in at the first row of the Table and delete rows below after

Copying content from web page creates error with or without .Document

Insert Missing Column in Worksheets and Paste Data onto new Excell

Tags