MS Excel -- replace data in a sheet with lookup values from another sheet using VBA

Hi all -- I have some text data stored in a column on an XLS sheet that I need to replace with some other values (numbers) via VBA. I've thought about a few ways of doing this but haven't hit on anything elegant yet. were it not for the fact that the data itself needs to be outright replaced, I'd use VLOOKUP, but that is not an option. I need to do it using VBA too.

I've attached a dummy data file. In this file, sheet "data_sheet" contains my data; column Q4 contains the specific data that I want to replace. Sheet "lookup_sheet" contains a listing of the text values that I want to lookup and their matching numeric values that I want to replace them with on data_sheet. Extending the example, row 4 of data_sheet contains a column Q4 value of "not so important'" which I want replaced with the value 4 (the matching value for "not so important" in column Q4_attribute_value on lookup_sheet). Row 6 contains a Q4 value of "kinda important" which correlates to a numeric value of 5 on lookup_sheet.

I'm using the XLS file as a means to convert data that I'm importing from other sources, so the number of rows in data_sheet will always vary, as will the actual values in each of the columns. That said, the values in Q4 will always be one of the lookup values on lookup_sheet.

This would not be a problem for me in Access, but my skills in Excel are poor at best, so any help with VBA code to do this reliably and repeatably would be GREAT.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
You could just use a lookup in a spare column and then copy and paste as values to replace the original values.

eg column I: =IFERROR(VLOOKUP($F2,lookup_sheet!$A$1:$B$7,2,FALSE),"")

Then copy column I and paste as values into column F. When pasted the IFERROR part of the formula will kick in changing the result in column I to "".
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...
Sub LookupText()
Dim wsData As Worksheet, wsLookup As Worksheet
Dim dlr As Long, llr As Long, i As Long
Dim x, y, z(), dict

Application.ScreenUpdating = False

Set wsData = Sheets("data_sheet")
Set wsLookup = Sheets("lookup_sheet")

Set dict = CreateObject("Scripting.Dictionary")

dlr = wsData.Cells(Rows.Count, "F").End(xlUp).Row
x = wsData.Range("F2:F" & dlr).Value
ReDim z(1 To UBound(x, 1), 1 To 1)

llr = wsLookup.Cells(Rows.Count, 1).End(xlUp).Row
y = wsLookup.Range("A2:B" & llr).Value

For i = 1 To UBound(y, 1)
    dict.Item(y(i, 1)) = y(i, 2)
Next i
i = 0
For i = 1 To UBound(x, 1)
    If dict.exists(x(i, 1)) Then
        z(i, 1) = dict.Item(x(i, 1))
        z(i, 1) = x(i, 1)
    End If
Next i

wsData.Range("F2").Resize(UBound(z), 1).Value = z

Application.ScreenUpdating = True

End Sub

Open in new window

In the attached, click the button called "Lookup" on Data sheet to run the code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
alfamikefoxtrotAuthor Commented:
Thanks to both of you for commenting. Subodh's solution is the one that most closely matches what I need. Have a great day...!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
Thanks, you too!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.