Vlookup with replace function

[contra76]

Hi,

This might seem very elementary, but how could I do a lookup that populates the original fields?

Example:
Column A in worksheet "one" has abbreviated names.

Column A in worksheet "two" has abbreviated names as well. Column B in worksheet two has the full names.

I need to vlookup the names in Column B (worksheet 2) to replace the abbreviated names in Column A in worksheet one.

Is this possible?
Thank you!

[MarvinP]

I'd create a new column on worksheet 1 and put the lookup formula in it. this would lookup the full names from worksheet 2 and put them in the correct rows on Sheet2. Then I'd do a copy and Paste VALUES ONLY over the abbreviated names on sheet 1.

I hope that makes sense. There may be other ways.

[contra76]

Thanks MarvinP.

I know that's the traditional way of doing it. I was hoping I wouldn't have to create a second column but if it's necessary that's fine.

I was hoping for help in writing a macro that would do this? I guess I would have to count the number of rows, populate the vlookup formula in column 2, and then have the macro paste values only over the abbreviated names.

This seems standard; anyone know of a pre-written macro out there?

[daksh1981]

Hi,

Hope the attached file would help you in this case.
Please let me know if you need any help on this.

Regards,
Daksh

[MarvinP]

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

[MarvinP]

I see that daksh1981 has also posted code that may do what you want.
Now you have two different answers to what you wish.

I use a For Next loop and he uses a Do While loop.
I use variable names like RowCtr and LastRow, he uses variables like i and j.
I go to the Last Row in Column A, he will stop as soon as there is a blank cell in Column A.
I used Option Explicit so all my variables are defined. He didn't Dim variable j.
I indented my code, he didn't.
He gave you a message when he was done. I didn't.
He turned off screen updating so it would run fast, I didn't.

There are a lot of ways of accomplishing the same thing using VBA.

I suggest you study and understand both methods. Look at the code and put the cursor behind a word and press F1 and help will explain what the word means.

Have fun learning VBA.

As you see there are lots of ways to accomplish tasks. Thanks to daksh for showing us both a second way.

[daksh1981]

Hi MarvinP,
Thanks for the kind words. VBA is a way of executing your logic for a given problem. Every person has their own logic . I really liked your coding also.

Regards,
Daksh