VBA to replace redacted with full number

[wwalters]

On Sheet 1, I have a list of information, with Column B containing redacted numbers. The underacted numbers are in Column B on Sheet 2.

The two sheets will not be in the same order. I need help forming the code to replace redacted in sheet 1 with redacted in sheet 2. To make a match, the last 4 will numbers will match between the two and Column A will match as well. I think I could get by just matching the last 4 (I do not believe there are any duplicates, but would love a second verify that A matches).

I am lost. I have tried several things, none of which have worked.

 

[AtlantaAccessAndExcel]

Post the code you have and we can help you.

You can get the last 4 characters by using the RIGHT function.

You can use the FIND function to search:

    Dim Rng As Range
    Dim FoundRow As Long
    With ThisWorkbook.Worksheets(<sheetnamehere>).Range("B:B")[INDENT]Set Rng = .Find(What:=<searchstringhere>, _
</searchstringhere>[/INDENT]
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
<searchstringhere>         If Not Rng Is Nothing Then
</searchstringhere>[INDENT]   FoundRow = Rng.Row
Else
   'not found[/INDENT]
<searchstringhere>         End If
</searchstringhere>
</sheetnamehere><sheetnamehere><searchstringhere>     End With</searchstringhere></sheetnamehere>

 

[wwalters]

With Worksheets("Sheet2")
fullno = Range(.Cells(1, 1), .Cells(80, 1))
End With
With Worksheets("sheet1")
Redacted = Range(.Cells(1, 15), .Cells(80, 15))
For i = 1 To 80
num = Right(Redacted(i, 1), 2)
For j = 1 To 80
If num = Right(fullno(j, 1), 2) Then
Redacted(i, 1) = fullno(j, 1)
Exit For
End If
Next j
Next i
Range(.Cells(1, 15), .Cells(80, 15)) = Redacted
End With

 

[AtlantaAccessAndExcel]

Sub Replace()


'assumes each sheet has 80 lines.
For InRow = 1 To 80
    InA = Worksheets("SheetA").Cells(InRow, 1)
    Redacted = Left(Worksheets("SheetA").Cells(InRow, 2), 4)
    
    For FullRow = 1 To 80
        'match column A
        If InA = Worksheets("SheetB").Cells(FullRow, 1) Then
            'match column B (partial)
            If Redacted = Left(Worksheets("SheetB").Cells(FullRow, 2), 4) Then
                'we have a match
                Worksheets("SheetA").Cells(InRow, 2) = Worksheets("SheetB").Cells(FullRow, 2)
                GoTo NextInRow
            End If
        End If
    Next FullRow
    
    'if you need to do something when there is no match, put that code here


NextInRow:


Next InRow


End Sub