Solved

Excel VBA Query

Posted on 2013-06-04
14
196 Views
Last Modified: 2014-05-23
I have an excel spreadsheet with data that i am look to execute a query to filter it. I want to filter the data in "Measures List" tab by executing a query on the "Input" tab. The issue is I don't know how to query on multiple variables for each criteria.

So for example, I want to be able to select High & Medium in the Impact Potential column and also a number of time frames in the "Total Time" column & also be able to search for a wildcard in the Focus Area & Responsibility columns.

Can anyone help me on this?
0
Comment
Question by:j8547
  • 6
  • 5
14 Comments
 
LVL 23

Expert Comment

by:Michael74
ID: 39221712
Without an example sheet it is hard to be specific but this may help

http://office.microsoft.com/en-au/excel-help/filter-by-using-advanced-criteria-HP005200178.aspx

Michael
0
 

Author Comment

by:j8547
ID: 39222118
Sorry forgot to attach the sheet. I have now.

All help is appreciated. I am looking for the VBA code for the query.
test.xlsx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39222161
You should populate a criteria range with your filtering data and the apply an advancedfilter to the data.  See the attached workbook for an example.

Also, I've done an EE podcast on advanced filtering, which is viewable on the EE YouTube channel, which you might want to watch.
http://www.youtube.com/watch?v=0M4gePK8510
test.xlsx
0
 

Author Comment

by:j8547
ID: 39222218
do you know how i can fit a wildcard in?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39222233
look at my criteria range on the first worksheet.  I used a wildcard.
0
 

Author Comment

by:j8547
ID: 39222253
the execute query didn't work so wasn't sure what was working
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:j8547
ID: 39222259
Sorry for tall the questions just can't find how you did it. how then can I add filter 2, 3, 4 and so on.

Thanks for the help
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39222271
I manually applied the AdvancedFilter.  Advanced Filter is one of your filtering options.

The video shows the VBA statements that can be used to apply an advanced filter.
0
 

Author Comment

by:j8547
ID: 39222663
is there a way i can see the manual advanced filter you applied?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39222687
Do you see the criteria range I created on the first sheet?
0
 

Author Comment

by:j8547
ID: 39223107
yes
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 39223413
I provided the data range and the criteria range to the AdvancedFilter dialog.  You can change the criteria cells (there are three rows, including the column name row) and reapply the Advanced Filter.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now