VBA Help - Scanning two Columns for string based on lookup table

[Johnny Thunder]

Hello All,

I am working on a project and was hoping for some help on the final piece of this puzzle.

I have a sheet Source that has two columns W or X that could contain a possible string of text that if its found I would like to place a value in column AL. I have a small lookup table on a sheet called Lookups that contains a mapping of the possible search strings and the desired replacement value that would go in column AL.

The desired result would be for a script that would Scan Column W on the Source Sheet using the lookup table and if a value matches a value from the lookup table, place the revised lookup value in Column AL, after scanning column W, if no values were found that matched the lookup table, then move on to column X on the Source Sheet and do the same search, if a value matches the lookup table drop in the revised lookup value in Column AL. There will never be a situation where there are two matching values in both W and X, the matching value will always only appear in one of the columns.

Hopefully my explanation was clear. Any help on this would be greatly appreciated!

Here is my sample Data:

Trial Balance - Working File.xlsm
	W	X
1	SAP Doc Header Txt	SAP Line Item Text
2	P12 18 Accrual Detail	P12 18 PDAA Movie Title
3	P12 18 Accrual Detail	P12 18 PDAA Movie Title
4	P03 19 Accrual Detail	P03 19PDAA Show Title
5	P03 19 Accrual Detail	P03 19PDAA Show Title
6	P06 19 Accrual Detail	P06 19PDAA Show Title
7	P06 19 Accrual Detail	P06 19PDAA Show Title
8	P12 19 Accrual Detail	PO9 19PDAA Show Title
9	P12 19 Accrual Detail	PO9 19PDAA Show Title
10	P03 20 Accrual Detail	PO3 20PDAA Show Title
11	P03 20 Acc	PO3 20PDAA Show Title
12	P03 20 Accrual Detail	PO3 20PDAA Show Title
13	P03 20 Acc	PO3 20PDAA Show Title
14	BS Movement 05/13/22 0222	BS Movement 05/13/22 0222
Source

And here is a sample of my Lookup Table

Trial Balance - Working File.xlsm
	O	P
1	Expense Mapping	Revised Value
2	Accrual	Accrual
3	Acc	Accrual
4	Publicity	Publicity
5	Pub	Publicity
6	Amort	Amort
7	Amortization	Amort
8	Rebate	Rebate
Lookups

 

[kevin9999]

Maybe this?

Option Explicit
Sub Johnny_Thunder()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim c As Range, d As Range
    Dim FindStrng As String, RepStrng As String
    Set ws1 = Worksheets("Source")
    Set ws2 = Worksheets("Lookups")
    
    For Each c In ws2.Range("O2", ws2.Cells(Rows.Count, "O").End(xlUp))
        FindStrng = c.Value
        RepStrng = c.Offset(, 1).Value
            For Each d In ws1.Range("W2", ws1.Cells(Rows.Count, "X").End(xlUp))
                If InStr(d, FindStrng) > 0 Then ws1.Cells(d.Row, 38) = RepStrng
            Next d
    Next c
End Sub

 

[Johnny Thunder]

Thanks for the Response Kevin, that actually worked out great!

 

[kevin9999]

Thanks for the Response Kevin, that actually worked out great!

Glad it helped & thanks for the feedback