windows - VBA Lookup by Multiple Criteria - Then Copy & Paste

07
2014-07

AJ Kim

Currently I have a 2 way database with 10+ fields and 1,000+ rows. Some fields are customer name, car model, year of model, price, date of purchase.

I would like to lookup rows of data by multiple criteria (i.e. looking up car sales by customer name, car purchased, and date) and then have the relevant rows copy and pasted from the data worksheet to the output macro output worksheet.

I need to lookup the rows of data by multiple criteria and some searches will return multiple rows that match the criteria.

I want to be able to push a button on the macro output worksheet allowing me to enter in customer name, car model, and date and then have the macro copy and paste the row(s) from the data worksheet to the macro output worksheet.

Answers

Mike Honey

I would use the Excel Table feature, and not create a macro.

I would convert the "database" from a range to an Excel Table (Select range, Insert Ribbon / Table).

Then I would use the Filter buttons that will appear next to each column heading to filter the data as required. Multiple criteria can be used and it will show all matching rows.

Related Answers

dbush

James, If it were me, I would keep this as simple as possible. I'd just have a brightly colored box somewhere out of the way with these guidelines and the link.

If you're interested though, here are steps you can follow to insert the VBA to create a pop-up rectangle text box next to the currently selected cell with whatever text you want it to say:

Open your workbook in Excel.
Select Insert menu > Picture > Autoshapes. Select a rectangle.
Place one rectangle anywhere on the sheet you want to work with.
Edit the text and make it say whatever you like, including a hyperlink.
Select the frame of that rectangle (not the text inside) and then find the name box in the upper-left corner of Excel. It will probably be named something like "Rectange11" now. Change the name to, "Guideshape" (since that's what the code looks for). 6.Right-click the sheet's tab at the bottom of Excel and select View Code.

Paste this code into the big empty text area on the right side of the screen:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False

    On Error Resume Next

    Shapes("GuideShape").Visible = msoFalse

    If Not Intersect(Target, Range("C5:C8")) Is Nothing Then

        With Me.Shapes("Guideshape")
             .Visible = msoTrue
             .Top = Target.Top
             .Left = Target.Left + 150
        End With
    End If

    On Error GoTo 0

    Application.EnableEvents = True

End Sub

Look for the line that has ("C5:C8") and change that include your range of editable cells where this message should pop-up. Keep the parentheses and double-quotes!
Close the VB editor and return to Excel.

Now whenever you click in a cell in the range you entered into the code, the rectangle you created should appear to the right of that cell. Play with the TOP and LEFT numbers in the code to change that position.

Good luck!

Lance Roberts

No, you can't create a hyperlink in a comment. You can only insert the URL text.

Hyperlinks is a property of a Range or Worksheet (which comments aren't). There's also a Hyperlink property for a Shape, but that wouldn't apply here (though there might be some really tricky way you could have an image cover the commment and provide the hyperlink).

mtone

I don't think Excel supports hyperlinks in comments. VBA will likely not help you there.

Could you not put URLs on a column next to your cells instead?

If you're strongly determined to have some sort of tooltip link, you could probably program a tooltip behavior from scratch in VBA (create little yellow form, place it next to cell, hide it when user clicks on another cell, etc..) but that would work separately from the real comments, and probably a few things wouldn't integrate very well, and probably not a very good idea anyway.

Home

windows - VBA Lookup by Multiple Criteria - Then Copy & Paste