Wilder1626
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.
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Ejgil Hedegaard. I will go with this option. It does exactly what i wanted.
Simpler, not as fast, but more flexible, because the ranges are selected in the function arguments.
See column G.
Wilder-Find-Options.xlsm