asked on

VBA Find and Replace using data table

Would like to do the following using VBA

A: Find and Replace the values in named range ("BizClass"), with its corresponding value in another named range ("occupancy").

Sheet1, Column D = NamedRange "BizClass"
Occupancy Tab: Contains a list of matching data.

Objection: Find the value in "Biz Class" named range in the data in 1st column of "Occupancy" named range and replace it with the value that is in the 3rd column.
Find-Replace.xlsm

Subodh Tiwari (Neeraj)

First change your Named Range BizClass to this...

=OFFSET(Sheet1!$D$2,,,COUNTA(Sheet1!$D:$D)-1)

Open in new window

Then try this...

Sub FindAndReplace()
Dim x, y, dict
Dim i As Long
x = Range("BizClass").Value
y = Range("occupancy").Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(y, 1)
    dict.Item(y(i, 1)) = y(i, 3)
Next i
For i = 1 To UBound(x, 1)
    If dict.exists(x(i, 1)) Then
        x(i, 1) = dict.Item(x(i, 1))
    End If
Next i
Range("BizClass").Value = x
End Sub

Open in new window

In the attached, click the button called "Find & Replace" on Sheet1 to run the code.
Find-Replace.xlsm

kateebebe

ASKER

That works almost exactly as needed. THere are instances where there would only be one line item to do the "Find/Replace" on. It seems this only works if data expands beyond row 2. Could this be changed?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kateebebe

ASKER

That is perfect!! Thank you so much for your quick assistance. It is appreciated.

Subodh Tiwari (Neeraj)

You're welcome! Glad I could help.
Thanks for the feedback.