Snasui Excel Forum

Code: Select all

Public Sub find_value()

Dim end_row As Long

Sheets("Sheet1").Select
Range("a1").Select

end_row = ActiveCell.End(xlDown).Row
       
Dim val As String
Dim i As Long

    For i = 1 To end_row
        val = Range("a" & i).Value
        Sheets("Sheet2").Select
        Columns("a:a").Select
        On Error GoTo skipfind:
            Selection.Find(What:=val).Select
        On Error GoTo 0
        ActiveCell.Offset(0, 1).Copy
        Sheets("Sheet1").Select
        Range("B" & i).Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
           
skipfind:       Sheets("Sheet1").Select
        
    Next
    
End Sub

Code: Select all

Public Sub find_value()
Dim end_row As Long
Sheets("Sheet1").Select
Range("a1").Select
end_row = ActiveCell.End(xlDown).Row
Dim val As String
Dim i As Long
    For i = 1 To end_row
        val = Range("a" & i).Value
        Sheets("Sheet2").Select
        Columns("a:a").Select
        On Error GoTo skipfind:
            Cells.Find(What:=val).Select
        On Error GoTo 0
        ActiveCell.Offset(0, 1).Copy
        Sheets("Sheet1").Select
        Range("B" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues
skipfind: Sheets("Sheet1").Select
    Next
End Sub

Code: Select all

Sub CopyData()
Dim cell As Range
Dim rw As Long
For Each cell In Worksheets("Sheet1").Range("A:A").Cells
If cell <> "" Then
rw = Lookup(cell.Value)
If rw <> 0 Then
Worksheets("Sheet1").Cells(cell.Row, "A").Resize(, 2).Value = _
Worksheets("Sheet2").Cells(rw, "A").Resize(, 2).Value
End If
End If
Next
End Sub
Function Lookup(item As String) As Long
On Error Resume Next
Lookup = WorksheetFunction.Match(item, Worksheets("Sheet2").Range("A:A"), False)
On Error GoTo 0
End Function