VBA Lookup by Multiple Criteria - Then Copy & Paste Row?

Microsoft Office excel 2003
June 24, 2014 at 07:05:45
Specs: Windows XP
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.


See More: VBA Lookup by Multiple Criteria - Then Copy & Paste Row?

Report •

#1
June 24, 2014 at 13:16:00
I attacked this problem in a different manner, without using VLOOKUP or Copy/Paste.

Instead, I used the Advanced Filter feature and created a macro to accept user input as the Criteria.

I started with the example shown at this site:

http://www.excel-easy.com/examples/...

After setting up Sheet 1 with the List Range (A5:D19) and the Criteria Range (A1:D2) as shown in that example, I then wrote the following code to present InputBoxes to the user, asking for the Country and Qtr.

Once the user enters the criteria, those values are stored in the Criteria Range on Sheet 1 and the code produces a Filtered list on Sheet 2.

At this point it's pretty basic, but I think it shows what can be done once more detailed is supplied.

Sub MyFilteredResults()
'Clear Sheet 2
  Sheets(2).Cells.ClearContents
'Get Country from user
   myCountry = Application.InputBox("Enter Country")
'Store Country In Sheet 1 Criteria Range
     Sheets(1).Range("C2") = myCountry
'Get Qtr from user
   myQtr = Application.InputBox("Enter Qtr")
'Store Qtr In Sheet 1 Criteria Range
     Sheets(1).Range("D2") = "Qtr " & myQtr
'Produce Filtered Results
    Sheets(1).Range("A5:D19").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets(1).Range("A1:D2"), _
        CopyToRange:=Sheets(2).Range("A1"), _
        Unique:=False
End Sub

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

message edited by DerbyDad03


Report •

#2
June 24, 2014 at 14:47:01
Thank you this is great! But two conflicts:

1) Am I able to do this lookup for also when I have 1 of the 2 criteria?

2) I believe I conducted it correctly and it did not paste into sheet 2. Any idea of what I am doing wrong?


Report •

#3
June 24, 2014 at 17:47:44
1) Am I able to do this lookup for also when I have 1 of the 2 criteria?

In my simple example, if you leave a criteria question blank, it should return the filtered list for just the single criteria entered. That's what it does for me.

2) I believe I conducted it correctly and it did not paste into sheet 2. Any idea of what I am doing wrong?

It's hard to say what you did wrong since I can't see your workbook from where I am sitting. I just set up Sheet1 to be an exact duplicate of the example shown at the site I linked to. I then copied the code that I posted in my response into the VBA editor, placing it in a standard module. When I ran the code and entered a Country and/or Qtr, the filtered results were placed in Sheet 2.

The only thing I can add is that the input for the Qtr criteria is just a number since the the code adds the string Qtr before placing the criteria in D2. i.e. If you enter 4 in the InputBox, the code will place Qtr 4 in D2.

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


Report •

Related Solutions

#4
June 25, 2014 at 03:56:46
So I re-did it and got it to copy, but instead of copying the filtered results it copies the entire database over 0_0. Does that mean the advanced filter isnt running?

Report •

#5
June 25, 2014 at 04:15:12
Honestly, I don't know what your issue is. I wrote and tested the code on a machine running Excel 2010 at work. Then when you said it didn't work, I tested it on a machine running 2013 at home, copying the code that I posted in this forum. It works fine for me on both machines.

As far as I know, there is no reason that it shouldn't work in Excel 2003 but it's not something I can test.

Have you tried doing an Advanced Filter manually just to make sure it works and that your criteria is correct?

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


Report •

#6
June 25, 2014 at 04:41:22
Another thought...

Are you trying my macro against the data found in the website I linked to, with Sheet 1 set up exactly as shown on that website? That is the only way it is going to work. It was just an example to show what is possible.

If you are running the code against a different range with different column headings, etc. it won't work.

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

message edited by DerbyDad03


Report •

#7
July 1, 2014 at 09:58:30
I just re-did the whole process and it worked. I have no idea what wasn't working before but I'm not going to question it. Thanks again.

Report •

Ask Question