Filter #10

Hi
We have the following code that filters a range in database(no problem)
this code is looking at column A which is a week number(11)
what we now need to do is extend this code to filter two ranges
the second range is column H and is days of the week(Monday)

Is it possible to expand this code to do this?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 3 Then
Sheets("data2").Range("Criteria").Calculate
  Worksheets("data2").Range("Database") _
    .AdvancedFilter Action:=xlFilterCopy, _
      CriteriaRange:=Sheets("data2").Range("Criteria"), _
      CopyToRange:=Range("A6:i6"), Unique:=False
Sheets("Data Entry").Range("D2").Calculate
End If
End Sub

thanks in advance

Kevin
0
kevin9285 (10)
8/20/2004 11:01:34 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
301 Views

Similar Articles

[PageSpeed] 28

Change your Criteria range to include both fields

Then, you'd need a second cell in which to choose the criterion for the 
Weekday field.

In the code, check for both addresses, e.g.:

   If Target.Address = "$C$2" Or Target.Address = "$D$2" Then


kevin carter wrote:
> Hi
> We have the following code that filters a range in database(no problem)
> this code is looking at column A which is a week number(11)
> what we now need to do is extend this code to filter two ranges
> the second range is column H and is days of the week(Monday)
> 
> Is it possible to expand this code to do this?
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Row = 2 And Target.Column = 3 Then
> Sheets("data2").Range("Criteria").Calculate
>   Worksheets("data2").Range("Database") _
>     .AdvancedFilter Action:=xlFilterCopy, _
>       CriteriaRange:=Sheets("data2").Range("Criteria"), _
>       CopyToRange:=Range("A6:i6"), Unique:=False
> Sheets("Data Entry").Range("D2").Calculate
> End If
> End Sub
> 
> thanks in advance
> 
> Kevin


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/20/2004 11:25:55 AM
Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<4125DFC3.2030806@contexturesXSPAM.com>...
> Change your Criteria range to include both fields
> 
> Then, you'd need a second cell in which to choose the criterion for the 
> Weekday field.
> 
> In the code, check for both addresses, e.g.:
> 
>    If Target.Address = "$C$2" Or Target.Address = "$D$2" Then
> 
> 
> kevin carter wrote:
> > Hi
> > We have the following code that filters a range in database(no problem)
> > this code is looking at column A which is a week number(11)
> > what we now need to do is extend this code to filter two ranges
> > the second range is column H and is days of the week(Monday)
> > 
> > Is it possible to expand this code to do this?
> > 
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Row = 2 And Target.Column = 3 Then
> > Sheets("data2").Range("Criteria").Calculate
> >   Worksheets("data2").Range("Database") _
> >     .AdvancedFilter Action:=xlFilterCopy, _
> >       CriteriaRange:=Sheets("data2").Range("Criteria"), _
> >       CopyToRange:=Range("A6:i6"), Unique:=False
> > Sheets("Data Entry").Range("D2").Calculate
> > End If
> > End Sub
> > 
> > thanks in advance
> > 
> > Kevin

thanks for reply the code a treat

however this is not returning the results we were hoping for
at present this returns all the occurences of both conditions
what we now need to do is filter the data on a time value and day
ie 06: - 07:00  and Tuesday
only the occurances of this condition should be returned

Can you help please

kevin
0
kevin9285 (10)
8/23/2004 7:44:06 AM
If the criteria range has both fields on the same row, the filter should 
only return records where both criteria are met.

kevin carter wrote:
> Debra Dalgleish <dsd@contexturesXSPAM.com> wrote in message news:<4125DFC3.2030806@contexturesXSPAM.com>...
> 
>>Change your Criteria range to include both fields
>>
>>Then, you'd need a second cell in which to choose the criterion for the 
>>Weekday field.
>>
>>In the code, check for both addresses, e.g.:
>>
>>   If Target.Address = "$C$2" Or Target.Address = "$D$2" Then
>>
>>
>>kevin carter wrote:
>>
>>>Hi
>>>We have the following code that filters a range in database(no problem)
>>>this code is looking at column A which is a week number(11)
>>>what we now need to do is extend this code to filter two ranges
>>>the second range is column H and is days of the week(Monday)
>>>
>>>Is it possible to expand this code to do this?
>>>
>>>Private Sub Worksheet_Change(ByVal Target As Range)
>>>If Target.Row = 2 And Target.Column = 3 Then
>>>Sheets("data2").Range("Criteria").Calculate
>>>  Worksheets("data2").Range("Database") _
>>>    .AdvancedFilter Action:=xlFilterCopy, _
>>>      CriteriaRange:=Sheets("data2").Range("Criteria"), _
>>>      CopyToRange:=Range("A6:i6"), Unique:=False
>>>Sheets("Data Entry").Range("D2").Calculate
>>>End If
>>>End Sub
>>>
>>>thanks in advance
>>>
>>>Kevin
>>
> 
> thanks for reply the code a treat
> 
> however this is not returning the results we were hoping for
> at present this returns all the occurences of both conditions
> what we now need to do is filter the data on a time value and day
> ie 06: - 07:00  and Tuesday
> only the occurances of this condition should be returned
> 
> Can you help please
> 
> kevin


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/23/2004 10:59:12 AM
Reply:

Similar Artilces:

Work Hours 10-25-06
Hi there, I want to change the default work hours setting for users of the system. At the moment they are set to work all day everyday of the week. Realistically I want this to be 9 a.m. to 5 p.m. Monday to Friday. Is there any way I can change this using customization and have it apply to all users? Pergaps it is a system setting I can change rather than customize? Many thanks, Mark Mark, Go to Settings | Business Unit Settings | User and select a user (can also go Service calander and then select the user from there as well) Then view the Work Hours, and select Set Up | New Weekly S...

deleted emails 03-10-10
how do i retrieve deleted emails from two specific email addresses? sort the deleted items folder on FROM address "blmc" wrote: > how do i retrieve deleted emails from two specific email addresses? ...

IE8 text size 01-25-10
The situation described occurs in IE8 on both Vista home premium and Windows 7 professional. Using the page options, I set the text size to smaller. It will remain that way for a while but within hours or sometimes a day the text size reverts to medium. Any suggestions Lou ...

Can't send or receive 07-27-10
I set up Windows Mail to my gmail account. I've double checked the settings, and they are correct. I got the following message when I tried to send an e-mail: The message could not be sent. The authentication setting might not be correct for your outgoing e-mail [SMTP] server. For help solving this problem, go to Help, search for "Troubleshoot Windows Mail", and read the "I'm having problems sending e-mail" section. If you need help determining the proper server settings, please contact your e-mail service provider. The rejected e-mail address ...

Filter #17
Have a spreadsheet, with column A: Date Column B: Daily Takings Column C: Profit and Column D: % All data is recorded on a daily basis for every day of the financial year. A summery report for data is made on the same sheet for the total of the financial year, which is easy enough to do for the entire year. How do I filter so that only data appears in the summery report from lets say July 1 - July 21....or any other dates I choose..........ie: is it somehow possible to enter Begiining Date in a cell and End dat in another cell, so it filters the Date column and only have that dat appea...

List output to a x by 10 range
I have a list that x lines long. I want to transpose that list to another range (starting on cell M1) that is 10 columns wide and however many rows long. I know this is super simple. Does it have something to do with range resizing? No doubt you've already experimented with macro recorder, discovering the standard transpose command wont wrap within a range. So we need handle the transposition manually. Presuming your data begins in cell A2: Sub test() For Each c In Range("A2", "A" & Range("A65536").End(xlUp).Row) Row = Row + 1 ...

Business Portal 4.0 & GP 10.0 Smartlist
Hi, Is there a way to use our GP 10 smartlists in the query sections of Business Portal 4.0 ? I've look at the Business Portal query builder and I don't think you can use SQL (in this case I could have extract Smartlists SQL). You have to used predefined entities. Maybe I get it wrong ? Thanks in advance for any help ! Richard, Thanks for using the newsgroups. You are correct. You need an entity for querying in Business Portal. Thanks Rob -- Rob Bernhardt Software Quality Assurance Microsoft Business Solutions This posting is provided "AS IS" with no warranties...

Filter #10
Hi We have the following code that filters a range in database(no problem) this code is looking at column A which is a week number(11) what we now need to do is extend this code to filter two ranges the second range is column H and is days of the week(Monday) Is it possible to expand this code to do this? Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 2 And Target.Column = 3 Then Sheets("data2").Range("Criteria").Calculate Worksheets("data2").Range("Database") _ .AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=She...

Outlook security #10
Hi, I have Office 2003 with Outlook 2003 running on Windows 2000. When sending emails from a different program, I can set Outlook security to bypass further warnings for up to 10 minutes. A customer, also with Outlook 2003, but updated from 2002, has to respond to every email with a 5 second pause. My questions: Why the different behaviour? How do I change the latter behaviour to the former? Thanks Thomas Hi Thomas, The security settings for Outlook 2003 can be found using the Tools->Options->Security Tab and security zone settings button. It works exactly the same way Internet ...

Office 2010 Beta 02-24-10
I have installed 2010 Beta and like it but I no longer can click on a file to open. When I try to associate the program to use none of the office programs show up. Is there a way aroung this? Microsoft Office Diagnostics perhaps, Or select Browse and look for the exe file. thom kamp wrote: > I have installed 2010 Beta and like it but I no longer can click on a file to > open. When I try to associate the program to use none of the office programs > show up. Is there a way aroung this? ...

2003 Filter and Sort Ascending
I have a database with over 2000 entries. I generated the database in excel 2007, but my clients only have 2003. When they sort the zipcode field in ascending order in 2003 (using the filter drop-down), the database only sorts some of the data and not all of it. How do I solve the problem of the filter sort ascending to capture all of the values in the database? Rob If full column is selected the sort should be OK. Don't let Excel guess your sort range. You may have a blank row. In addition................... Excel 2003 will show only the first 1000 unique items in t...

integration manager 10.0-SQL Data Source
We are trying to run an integration by using a link to the GP tables. One of our data sources is using the SQL Server as the data source. We can query the data source and return all rows in the integration. However, when we run the integration we receive the error: An error handler was not found for: Object reference not set to an instance of an object. We are on the latest service pack too. Are you using any field script or event script in IM? Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogst...

windows media player 02-07-10
when trying to play a dvd this message appears. windows media player cannot dvd video. I have installed decoders. How can i get it to play my dvds On Sat, 6 Feb 2010 16:29:01 -0800, lmorris <lmorris@discussions.microsoft.com> wrote: >when trying to play a dvd this message appears. >windows media player cannot dvd video. >I have installed decoders. >How can i get it to play my dvds Do you have suitable DVD decoders ? Only a subset are media center compatible (the feature you need to look out for) DecCheck for XP, and the Vista VMCD utility can help here : ...

Junk Mail Filters #3
Props to Microsoft for doing a FABULOUS job on the new junk mail filters in Outlook 2003 THANK YOU VERY MUCH!!! Dave anonymous@discussions.microsoft.com wrote: > Props to Microsoft for doing a FABULOUS job on the new > junk mail filters in Outlook 2003 > > THANK YOU VERY MUCH!!! > > Dave Dave, are you insane? They suck. I have several reasons why they suck. I wish MS would fix it. ...

Filter Frustration
I have two forms. Both have a button that call the same subform. This is the information for the first call. It works just fine. ________________________________________ stLinkCriteria = "[SEASONS]='" & Me!Season & "' And [DevCodeA]='" & Me!DEVCODE & "'" stDocName = "MilestoneDates" DoCmd.OpenForm stDocName, , , stLinkCriteria ________________________________________ This is the information for the second call. The Seasons portion works but the TrackingNo portion doesn't (Ie I pulled the c...

Multiple Commas 02-11-10
Some people decided to play a cruel joke and pulled two commas into a name field and i need to split the name into a first and last name column. Normally i could do this but the second comma is throwing me off. It looks like this: last name,first name, Ive tried doing a replace on the last comma but its not working. Does anyone know any tricks on how to either get rid of that second comma or split the field into two seperate columns as it is? Thanks for any help. dim aName() as string dim strName as string dim strLastName as string dim strFirstName as string ' just ...

PRODUCT KEY 04-05-10
I've just purchased this hp and the cd that was provided to me with the product key sticker on front does not work. I was under the impression that there would be some type of trial period that would allow me to use the product to determine if I want it or not. I am unable to open any documents in word on my cpu because this software has somehow locked me out of my papers for school. I can't edit copy or even view the docs w/o the product key. The product key provided is 7X4P3 Q24MX KHRKH R6CX2 G76Y4 it clearly doesn't work rather it is in lower case or upper case for...

Can't receive mail 07-29-10
I have problems receiving mail. I've worked through the trouble shooting guidelines and nothing works. I get a Windows Security box requesting user name and password but entering the details doesn't fix the problem The strange thing is that sometimes after I have gone in and reentered details in the Accounts Properties - Server tab it will down load them BUT NOT EVERY TIME. The Error message below is the most common one that I get. Account: 'mail.bigpond.com', Server: 'mail.bigpond.com', Protocol: POP3, Server Response: '-ERR [IN-USE] account is...

Re: filter out all the #n/a...
I have done a vlookup in col(b), col(c) and col(d) i want to summarise column b,c,d in column E e.g if col b2 = #na, c2=#na, d2=300 then i want e2=300 if col b3 = 100, c2=#na, d2=#na then i want e2=100 how would i do this?? some sort of OR function? also is there a way to highlight/filter all the rows where b2=#na c2=#na, d2=#na ? this will tell me which rows that are not found in an of my vlookups -- Message posted from http://www.ExcelForum.com If you are getting #n/a as a result of the vlookup statements, tr putting in the following formula. This will produce "Not found&qu...

Stats microsoft.public.windowsxp.general (last 7 days) 07-05-10
"Caveat: Quantity is not necessarily a measure of Quality" Newsgroup.................: microsoft.public.windowsxp.general Stats Were Taken..........: Mon, 05 Jul 2010 08:15:20 GMT Stats Begin...............: Mon, 28 Jun 2010 08:26:44 GMT Stats End.................: Mon, 05 Jul 2010 07:03:30 GMT Days......................: 7 Total No. of Articles.....: 606 Total No. of Characters...: 1707259 Total Volume..............: 1667 Messages Per Day..........: 86.6 Characters Per Day........: 243894.1 Average Daily Volume......: 238 kB Total Posters This Week...:...

Word spell check 04-21-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How do I skip all repeated words. My documents intentionally use repeat words but the spell checker always stops at each one. The ignore all and the add functions are not available The spelling checker will always stop at repeated words. Your only option is not to use Spelling. Cheers On 21/04/10 4:34 PM, in article 59bb736f.-1@webcrossing.JaKIaxP2ac0, "amclean4@officeformac.com" <amclean4@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard...

Any way to filter cells with formulas ?
Hi, I need to filter cells with formulas and not to select them , is that possible by a simple or advanced filter ? Thank you . -- gaftalik ------------------------------------------------------------------------ gaftalik's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6450 View this thread: http://www.excelforum.com/showthread.php?threadid=488232 Maybe.... But I think you'll have to expand on what you really want... gaftalik wrote: > > Hi, > I need to filter cells with formulas and not to select them , is that > possible by a simple or a...

custom filtering/migration of .msg file contact data to .vcf format
I need a way to filter and process incoming sales contact data (name, addres, phone, etc) within incoming .msg files (sample .msg message body below) and append directly to my Outlook Contacts file (.vcf output) for future use. I receive this info from web queries emailed to me, and I'm looking for a way to automatically process every message contact in a format below - the volume of email could be very large, so it needs to be automatically executed by an outlook rule (I can setup a rule to apply only to these emails, based on the subject text, which can be assumed to be unique). I'...

How to filter rows with commom N�'s in any column
Hello, How is it posible to filter the common N� that may appear in an coloumn Examlpe: A B C D E F 2 6 8 10 15 20 1 2 5 16 25 30 4 8 12 19 45 48 2 4 15 20 25 28 6 9 19 29 39 49 8 19 25 28 41 45 In this example I want to filter all the rows that have the commo N�8 The filtered Result Should be shown as below: A B C D E F 2 6 8 10 15 20 4 8 12 19 45 48 8 19 25 28 41 45 Thank You Moti -- Message posted from http://www.ExcelForum.com Add a new column to the table, and...

MSDN 10 user license
As we're setting up our development and test environments for CRM, we're trying to determine which licenses are best to use. I know we can use the MSDN 10 user license, and that won't prompt me to register within 30 days. Is that time limited (e.g. expires after 30 days or 90 days)? We want true development and test environments where we can write our customizations before moving them into production, so don't want to be restricted to a time period. Thanks Ian The MSDN and Action Pack Licenses are "included" with your yearly subscription and hence they do not exp...