Solved How to write macro with multiple inquiries.

February 20, 2016 at 14:11:56
Specs: Windows 10
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?


See More: How to write macro with multiple inquiries.

Report •

#1
February 20, 2016 at 14:38:54
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:B3

Return 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.


Report •

#2
February 20, 2016 at 14:55:50
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.

Report •

#3
February 20, 2016 at 15:31:46
✔ Best Answer
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.


Report •
Related Solutions


Ask Question