Find and Replace call for help!

[Equious]

Hey;

I'm a self proclaimed simpleton when it comes to excel. I've been scouring the internet, but I've been unable to find QUITE what I'm looking for.

I'm looking for a formula or VB script that searches all the cells in Column A on Sheet 1 for all the values in Column A on Sheet 2, and when it finds a match, it replaces the value of Column I for the corresponding row, with the value in Column B for the corresponding row in sheet 2.

The macro seems necessary as there are hundreds of entries and the order doesn't necessarily match. Being able to consolidate this into one step would REALLY save time.

Thanks for any assistance.

PS: This is specifically in Excel 2007, if that is of concern.

<colgroup><col></colgroup><tbody></tbody>

 

[JoeMo]

See if this does it (untested):

Sub Equious()
Dim lR1 As Long, lR2 As Long, vA1 As Variant, vA2 As Variant, i As Long, j As Long, ct As Long
lR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
vA1 = Sheets("Sheet1").Range("A1:A" & lR1).Value
vA2 = Sheets("Sheet2").Range("A1:B" & lR2).Value
Application.ScreenUpdating = False
For i = 1 To UBound(vA1, 1)
    For j = 1 To UBound(vA2, 1)
        If vA1(i, 1) = vA2(j, 1) Then
            ct = ct + 1
            Sheets("Sheet1").Cells(i, "I").Value = vA2(j, 2)
            Exit For
        End If
    Next j
Next i
Application.ScreenUpdating = True
If ct > 0 Then
    MsgBox ct & " matches found"
Else
    MsgBox "no matches found"
End If
End Sub

 

[Equious]

See if this does it (untested):

Sub Equious()
Dim lR1 As Long, lR2 As Long, vA1 As Variant, vA2 As Variant, i As Long, j As Long, ct As Long
lR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
vA1 = Sheets("Sheet1").Range("A1:A" & lR1).Value
vA2 = Sheets("Sheet2").Range("A1:B" & lR2).Value
Application.ScreenUpdating = False
For i = 1 To UBound(vA1, 1)
    For j = 1 To UBound(vA2, 1)
        If vA1(i, 1) = vA2(j, 1) Then
            ct = ct + 1
            Sheets("Sheet1").Cells(i, "I").Value = vA2(j, 2)
            Exit For
        End If
    Next j
Next i
Application.ScreenUpdating = True
If ct > 0 Then
    MsgBox ct & " matches found"
Else
    MsgBox "no matches found"
End If
End Sub

Hey! .. I'm sorry it took so long to get back to you.. I hope I've not lost you. It SEEMS to function the way i want it to! It returned a lower number of matches than I would have expected however... is there a way to tweak this, such that, when a match ISN'T found, it outputs it to another place/list? .. That would actually make my day HAHA.