Hello, I am looking to build a macro which searches within a cell for one of multiple possible values and returns the value found, if any. Specifically, this macro will search within the Transaction column of banking detail and return vendor information from the inquiry search table.

Example:

Column A contains Transactions from the bank. Row 1 Column A contains "1/1/16 Amazon 564654654." Row 2 Column A contains "1/15/16 Barnes Nble 5054056." Row 3 Column A contains "1/20/16 65456Dry Queen."Column B contains the table the search will reference. Row 1 column B contains "Barnes." Row 2 column B contains "Dairy." Row 3 column B contains "Amazon."

This macro needs to search in cells A1:A3 and return the match from cells B1:B3. From there it will use a Vlookup to search in cells C1:C3 for the full name of the vendor (i.e. "Dairy Queen." "Barnes & Noble.")

How should I go about writing this?

re: Row 1 Column A, Row 2 Column A, etc.In the future, please just use A1, A2. etc. It'll be easier for you to type and easier for us to read.

re:

This macro needs to search in cells A1:A3 and return the match from cells B1:B3Return the match to where? In your example, the macro will find Barnes in A2. What now? What is the code supposed to do with the fact that it found a specific word in a specific cell?

Could there be multiple occurrences of the search strings in Column B? e.g. Might Barnes be found in more than one cell? If so, what is the code supposed to do with the multiple results?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Let me change the parameters slightly to make room for the results column. A1:A3 contain banking Transactions. C1:C3 contains the items to search for in A1:A3. The macro will search in A1 for any of the values in C1:C3. If it finds "Barnes," it will look for the corresponding value in D1:D3 and return the value "Barnes and Noble" in B1. Then it will look in A2 for the values in C1:C3, etc.

A1 contains "1/1/16 Barnes 5646"

C1 contains "Barnes"

D1 contains "Barnes and Noble"

The macro will place the result in cell B1.

This code should get you what you want. I added some instructions to deal with cells in Column A that do not contain any of the search strings.

You might want to review this How-To also:

http://www.computing.net/howtos/sho...

Sub FullNames() Dim A_cell As Range Dim C_cell As Range 'Loop through A1:A3 For Each A_cell In Range("A1:A3") 'Loop through C1:C3 until string is found For Each C_cell In Range("C1:C3") 'If string is found, VLOOKUP full name If InStr(A_cell, C_cell) > 1 Then Range("B" & A_cell.Row) = _ Application.WorksheetFunction.VLookup(C_cell, Range("C1:D3"), 2, 0) Exit For End If Next 'If no value found, place "No Name" in Column B, Fill with Yellow If Range("B" & A_cell.Row) = "" Then Range("B" & A_cell.Row) = "No Name Found" Range("B" & A_cell.Row).Interior.ColorIndex = 6 End If Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll