Vlookup with replace function

Hi Contra76,

Find the attached with VBA that will do as you wish. The old joke goes... be careful what you wish for.

You must run the code from Sheet1. If it doesn't find a match for the cell on Sheet1 it might overwrite it with an error message. You now need to figure out how to get this example to work with your data. Etc, Etc...

I hope giving you a loaded VBA program motivates you to learn VBA.

I suggest you set a breakpoint in the code and step through it slowly to make sure it does what you want before unleashing it on your good data. The following code is in the attached workbook.

Option Explicit

Sub Abrev2StateName()
    Dim RowCtr As Double
    Dim LastRow As Double
    Dim StateAbrev As String
    Dim WS2 As Worksheet
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Set WS2 = Worksheets("Sheet2")
    
    For RowCtr = 2 To LastRow
        StateAbrev = Cells(RowCtr, "A").Value
        Cells(RowCtr, "A") = _
            WorksheetFunction.VLookup(StateAbrev, WS2.Range("A2:B100"), 2, False)
    Next RowCtr

End Sub