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?
Subodh Tiwari (Neeraj)Connect With a Mentor 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.
Rob HensonConnect With a Mentor Finance 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 "".
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.