Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

VBA - VLookup and Replace InStr

Hi,

I have this Excel file with two sheets in it.
Sheet 1 and DBASE.

In Sheet1 column C, i will have multiple values inside the cells. They are called Options.
In Sheet DBASE, i will have the same distinct single Options values in column M and in column N i will have the Option description.

My goal is to replace in Sheet1 column C all the Options by the Option Descriptions from the DBASE sheet.

I started to build something but i just can make it right now.

Here is the code.
 Dim Question As Range
    Dim Found As Range
    Dim rngSearch As Range
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets("DBASE")   'User's answers sheet
    Set ws2 = Sheets("Sheet1")   '
    Set rngSearch = ws1.Range("M:M")    'Users questions
    
    Application.ScreenUpdating = False
    For Each SearchValue In ws2.Range("c1", ws2.Range("c" & Rows.Count).End(xlUp))
    
        If Not IsEmpty(SearchValue) Then
        
            Set Replace_By = rngSearch.Find(What:=SearchValue, _
                                       LookIn:=xlValues, _
                                       LookAt:=xlWhole, _
                                       SearchOrder:=xlByRows, _
                                       SearchDirection:=xlNext, _
                                       MatchCase:=False)
                
            If Not Replace_By Is Nothing Then
                If InStr(SearchValue.Offset(, 0).Value, Replace_By.Offset(, 0).Value) Then
                  SearchValue.Offset(, 0).Value = Replace(SearchValue.Offset(, 0).Value, SearchValue.Offset(, 0).Value, Replace_By.Offset(, 1).Value)
                Else

                End If
                Set Replace_By = Nothing
            End If
        Else
        End If
    Next SearchValue
    Application.ScreenUpdating = True

Open in new window


How can i do this? Can it be done with a code that would also run faster than the approach i'm taken?

Thanks for your help
vlookup-and-InStr-macro-sample.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Another method could be a VBA function.
Simpler, not as fast, but more flexible, because the ranges are selected in the function arguments.
See column G.
Wilder-Find-Options.xlsm
Avatar of Wilder1626

ASKER

Thank you Ejgil Hedegaard. I will go with this option. It does exactly what i wanted.