Replace data in one column

[shield5]

Hi,

I have a two long column of data that look like this:

B C

Alabama AL
Alaska AK
Arizona AZ

my goal is to find and replace the data in column A with the matching data in column C

For example if I type Alabama in cell A4 it will change to AL and so on.​

Is there a VBA macro to replace the data once entered in column A to make look like this?

A

AZ
AK
AL


Thanks

 

[wideboydixon]

I think this would achieve what you're after in a macro:

Public Sub ReplaceColumnA()

Dim lastRow As Long
Dim thisRow As Long
Dim foundRow

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For thisRow = 1 To lastRow
    foundRow = Application.Match(Cells(thisRow, "A").Value, Range("B:B"), 0)
    If Not IsError(foundRow) Then Cells(thisRow, "A").Value = Cells(foundRow, "C").Value
Next thisRow

End Sub

WBD