criteria field to auto filter

ok that seems to work but the problem now is that the screen is blank, if i select the filter the date in the field is correct with the right criteria but i have to select OK for it to work, how can i get it to select OK automatically so that will display all the transaction dates...

thanks for this



>>>>

The < operator should be enclosed in quotation marks, and followed by an 
ampersand:

  Selection.AutoFilter field:=4, Criteria1:="<" & lessdays


dhbyrne wrote:
> Hi,
> I am trying to write a macro that will use the autofilter function in Excel. I have a list of customers transactions and 
> I have a table with 5 columns, account no, account name etc and the 4th column is date of last transaction. The date of last transaction tells me the last time a customer has bought something off me. I use the record macro button and it gave me the following code
>>> selection.autofilter field:=4, criteria1:="01/01/2004", xlAnd.
>> I tried to modify the code but i keep getting errors.
> I want it to lost all the transactions that are greater that today - 30 days, so i had something like this
>> set Lessdays=date 'this gave me todays date
> lessdays=(lessdays - 30) ' this gave me todays date - 30 days wehich is the 15/12.2003
>> selection.autofilter field:=4, criteria1:= < lessdays , xlAnd.
> This gives me an error like end of expressen expected and it highlights the "<". I have tried many variations but none work
>> I also want to try and say greater than 30 days but less than 60 etc. 
>> I need this to watch my customers to ensure that they buy off me and i dont loose any.
>> thanks in advance
> 


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



0
anonymous (74722)
1/16/2004 3:16:22 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
663 Views

Similar Articles

[PageSpeed] 34

You could run a macro when the workbook opens. For example, place the 
following code in the ThisWorkbook module, and the filter on the "Data" 
sheet is updated when the workbook opens:

'==============================
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim lessdays As Date
Set ws = Sheets("Data")

lessdays = Date - 30

'check for filter, turn on if none exists
   If Not ws.AutoFilterMode Then
     ws.Range("A1").AutoFilter
   End If

'filter for current date - 30 days
ws.Range("A1").AutoFilter _
   Field:=4, Criteria1:="<" & lessdays

End Sub
'=====================================

dhbyrne wrote:
> ok that seems to work but the problem now is that the screen is blank, if i select the filter the date in the field is correct with the right criteria but i have to select OK for it to work, how can i get it to select OK automatically so that will display all the transaction dates...
> 
> thanks for this
> 
> 
> 
> 
> 
> The < operator should be enclosed in quotation marks, and followed by an 
> ampersand:
> 
>   Selection.AutoFilter field:=4, Criteria1:="<" & lessdays
> 
> 
> dhbyrne wrote:
> 
>>Hi,
>>I am trying to write a macro that will use the autofilter function in Excel. I have a list of customers transactions and 
>>I have a table with 5 columns, account no, account name etc and the 4th column is date of last transaction. The date of last transaction tells me the last time a customer has bought something off me. I use the record macro button and it gave me the following code
>>
>>>>selection.autofilter field:=4, criteria1:="01/01/2004", xlAnd.
>>>
>>>I tried to modify the code but i keep getting errors.
>>
>>I want it to lost all the transactions that are greater that today - 30 days, so i had something like this
>>
>>>set Lessdays=date 'this gave me todays date
>>
>>lessdays=(lessdays - 30) ' this gave me todays date - 30 days wehich is the 15/12.2003
>>
>>>selection.autofilter field:=4, criteria1:= < lessdays , xlAnd.
>>
>>This gives me an error like end of expressen expected and it highlights the "<". I have tried many variations but none work
>>
>>>I also want to try and say greater than 30 days but less than 60 etc. 
>>>I need this to watch my customers to ensure that they buy off me and i dont loose any.
>>>thanks in advance
>>
> 
> 


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

0
dsd1 (5911)
1/16/2004 6:28:48 PM
Reply:

Similar Artilces:

FROM field when sending messages
How do I get a FROM field to come up when I am sending or forwarding messages? The only options that come up are TO, CC, and SUBJECT. Before XP was installed on my computer, a FROM field came up as well, so I could send messages on my bosses' behalf. While composing, View--From Field Ray at work "Tammy" <anonymous@discussions.microsoft.com> wrote in message news:021f01c39cb1$2fa2d1d0$a101280a@phx.gbl... > How do I get a FROM field to come up when I am sending or > forwarding messages? The only options that come up are > TO, CC, and SUBJECT. Before XP was...

Calculated items/fields in pivot table
Dear all, I am new to excel pivot table and I would like to ask you all a question regarding the calculated items/fields in pivot table. I am making a pivot table whose data source is shown below: Team Salesperson Amount Sold Price each Team Person Amount Sold Price each Paper A 50 35 Paper D 60 25 Pen D 500 4 Desk C 20 400 Desk A 10 700 Pen B 700 3.5 Clip B 500 2.5 Clip C 600 2 Desk B 15 600 I am thinking if it is a way to add a field in the pivot table which can show the total sale of each Team. For e...

importing excel with alphanumeric figures into the same field
I have an excel file that I am trying to import into Access. One field with alphanumeric characters will not import. It just causes errors. It is listed as a double type import, but it will not do it. Please help. Hi Randy, The Access routine that imports Excel data doesn't allow direct control over the types of the fields it creates, and often runs into trouble with Excel columns that contain a mix of numeric and text values. You can work round this in any of the following ways: 1) create the table yourself with the field types you need, then import the spreadsheet data. The f...

Change font/colours for text in field
tHi, Is it possible to format the text displayed in a field with another font or colour? /Lotten Yes, there are several ways, however, they aren't officially supported by Microsoft as customization points. All the CRM screens and their controls have corresponding cascading style sheets defined in the CRMWeb folder of your CRM installation. There is no central spot for these sheets, and they are distributed amongst the subfolders of this directory. This distribution is arranged by component function. You can add css attributes to color various components in these sheets. You can also...

Items report shows negative -1 in Available quantity field
When I run the Items (all) report by clicking the "Items" button and sort the Available Qty. field on the report, I am finding -1 items in the Available Qty. field even though we are out of these items. When I drill into the properties of the item, and go to the Inventory tab, and look at the committed field, I find that RMS has a 1 in the field and a -1 in the Available field, yet On_hand has a zero. I would like to be able to zero out the Committed and the Available fields to zero, but would also like to know how this occured to begin with? We think that we may have had a...

Require Field Entry based On Other Entry
I'm trying to Require Entry in a Field on a form if another field has a certain entry. I've tried the following code in Before Update: If Me![Customer]="Mutual" and IsNull(Me![Returns]) Then MsgBox "You must enter a value in Returns." Cancel = True [Returns].SetFocus But it's giving me the error message: "Block IF without End IF" Thanks! That's one of the more accurate error messages If Me![Customer]="Mutual" and IsNull(Me![Returns]) Then MsgBox "You must enter a value in Returns." Cancel = True ...

RMS PO # mapped to field in SBA
In the RMS Connector from EVT, the RMS PO # should be mapped to either the Reference field or Memo field on the bill in Small Business Accounting. It is VERY difficult to track a PO between RMS and SBA without that. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. htt...

too many fields on import, or too few brain cells?
I am a self-taught novice so please bear with me: I am a teacher and have created a scantron form using Adobe Live Cycle. The form uses radio-button groups for students to enter their answers into. I export the data as an .xml file and then try to import the data into access which will create a new record with the student's name, date, and test ID, whereupon I will have some calculated fields which will generate the student's test score. My problem: There are just over 300 fields on the adobe form. The access limit is 255, which makes it impossible to import all of the data. My ...

How to Pass Filter from Query to Report?
Hello, I would appreciate any help with this problem that's been driving me nuts. I have a query of inventory items with only a subset of fields from the complete inventory datasheet. I have designed a report that will take the items from the query and print tags. Sometimes, I would like to print only a few tags. I apply a filter to one of the columns in the query datasheet, but this filter has no effect on the report. What can I do so that when I apply a filter to the query, it will also be reflected in the report? I was thinking of putting a formula of some kind in the filter pro...

Add Custom Field to an Account
I am trying to add a custom field to an 'Account' and don't see it appearing after restarting IIS. Here's what I did: Clicked on Goto > Settings > Customization > Customize Entities > Account > Attributes > New (FYI this is a date field called Renewal Date). After saving and publishing, I then restarted IIS on the CRM box, but don;t see the new field in any part of the Account sections. Thoughts? Thanks You will have to go back to the view of the account and add that field to the form. You can test the view. If it looks ok, then you publish the chang...

Filter form with 2 combo boxes error
Hi everyone, I have a little problem filtering two fields in a continuous form :( Private Sub filtro_Click() Dim ftrcand As String Dim ftrseccao As String ftrcand = "[desig_candidatura_a] = '" & Me!cand_a & "'" ftrseccao = "[gt design] = '" & Me!gt & "'" If ((Not IsNull(Me.cand_a)) And (((Not IsNull(Me.gt)) ))) Then Me.Filter = "[desig_candidatura_a] = '" & Me!cand_a & "'" and "[gt design] = '" & Me!gt & "'" Me.FilterOn = True End If Bo...

Linked Table
Hi, I have a Access 2003 mdb that I have used for years that has worked OK. Recently one of my queries malfunctioned to an external Progress db's linked table. Specifically, the query's table list is missing many of the fields in the table though when I inspect the linked table in the Tables Objects Section I see all the fields. How can I fix my query? TIA, Dan Why post the same question in different newsgroups? (already answered in the other newsgroups you posted in). If your situation is one of the (few) unusual circumstances in which it is necessary/appropriat...

Intelligent Message filter update problem with Junk e-mail folder
I've recently installed Intelligent Message filter on my front end server. All appeared to be working OK, so I went ahead, updated the server with the IMF update and restarted the server. Inbound external messages are now assigned an SCL rating upon delivery on the front end server, but ALL messages seem to be being sent to the Outlook Junk e-mail Outlook folder when SCL rating is above that of 0 and 1. My delivery and junk e-mail settings are both set to 8 under Global Settings - Delivery, but messages are automatically being moved to Junk e-mail folder if they are greater tha...

HTML Filtering
running exch 2003 on win2003 server. Barracuda is running on a seperate machine. Question is this: Why would emails, that contain html attachments, not be delivered even if they are in the whitelist(barracuda). There appears to be no delivery restrictions within exchange server, but am open to any suggestions of where to look. We are also running ISA 2000, but no rules are set for filtering SMTP. Thanks, In ESM under Internet Message Formats, Default format, Properties, Message Format Tab, MIME do you have the "both" radio button selected? "Ron_F" <RonF@d...

auto-tabbing
Hi folks! I found the perfect code yesterday for entering survey data and my form seemed to be working was working but now it doesn't. The first field is called "Q_No". In the "On Change" event for this field I have the following: If Len(Me![Q_No].Text) > 2 Then Me![S1S1].SetFocus End If When I enter a 3-digit number into "Q_No" the cursor moves to "S1S1" but it won't let me enter any numbers and the database crashes. If I bypass "Q_No" and enter a number directly into "S1S1" the tab works fine for the rest o...

Jet 708734 AFS-1B-CF Charcoal Filter for 708620B AFS-1000B Air Filtration System
Price:$62.00 Image: http://thediscountguru.info/image.php?id=B0002ZHBKK Best deal: http://thediscountguru.info/index.php?id=B0002ZHBKK just received today am actually using this jet air filtration system in my house in the house wanted to try this for smoking otherwize electrostatic filter is great for dust will see how this works for that but is not as pictured, comes paper framed and looks similar to electrostatic one without the metal support but actual filter is much stiffer Includes AFS-1B-CF Charcoal Filter for AFS-1000B - 708734 SIMILAR PRODUCTS: Jet 708732 AFS-1B-WOF Wash...

Avoiding duplicate data based on criteria for another field in a q
I look after the admin for our fishing club. I've created a database (access 2003) with around 1200 records. we have members and non members who fish with seperate tables for each. Ive been working on a method to show the takings from between two dates for the non members. I've achieved it with one date (eg todays, or yesterdays) but if I put dates in covering several days or weeks It serves up junk! The problem is to create a query which will show each date on which someone fished (between the criteria dates entered using a form into the "date of fishing" field in the...

y-axis auto range based on data limits
I have a spread sheet with numerous charts. I need the y axis on the charts to set the minimum appoximately 20% below the lowest data point and set the maximum appoximately 20% above the highest data point. The major unit should be set at appoximately 10% of the entire range of data. The spreadsheets are templates and will have a wide variety data placed into the cells. Calculate the appropriate values in some cells. Use this technique to apply the calculated values to the chart's axis: http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html - Jon ------- Jon Peltier, Mic...

How to autosum data from filter ?
Hi, I've filtered a column with figures in it to reflect a specifi client. How do I then autosum the filtered data without including all of th unfiltered data ? Thank -- conk ----------------------------------------------------------------------- conks's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3333 View this thread: http://www.excelforum.com/showthread.php?threadid=55958 Take a look at SUBTOTAL Functions. I know it ignore hidden cells. "conks" <conks.2amcpz_1152349201.9046@excelforum-nospam.com> wrote in message news:conks.2amc...

auto save in tool menu
In Office 2000 when I set the options in the Excel auto save feature, they only last for that session. Is there any way to make my settings the default? I don't use autosave, but does this help: http://support.microsoft.com/default.aspx?scid=kb;en-us;231117 XL2000: AutoSave Settings Are Not Retained Between Sessions of Excel 2000 Jim wrote: > > In Office 2000 when I set the options in the Excel auto > save feature, they only last for that session. Is there > any way to make my settings the default? -- Dave Peterson ec35720@msn.com ...

URGENT : Adding search criteria ..............
Hi, I have created a new field in contact form, now how can i add this newly added field in the search criteria. (ex) Added a new field in contact form named MemberId(string field). in the search box of contact form how can i add this new field in the search. Thanks in advance. Richard: What search box are you talking about - the "Find Contacts" box? I believe it only seraches the name fields. If you want to find contacts based on other fields (including custom ones) you need to use the advanced find (from the Tools menu). Dave "Richard I.P" <iamiamiam_77@yaho...

Auto-refresh pivot table?
Hi. Is there a way to set up a pivot table so it will automatically update when its source data changes. It seems that by default, I need to explicitly tell it to refresh. Thanks! Ken kk_oop@yahoo.com wrote: > Hi. Is there a way to set up a pivot table so it will automatically > update when its source data changes. It seems that by default, I need > to explicitly tell it to refresh. > > Thanks! > > Ken I'll answer my own question. Enter the following VBA code for the worksheet that contains the pivot table (get to the code by entering alt+F11): Private Sub W...

Auto add to distribution list by subject line
Is there a way to set up the ability to add incoming email address to a distribution list based on the subject line? I don't think natively, you would have to write your own sink or maybe buy some third party software, unfortunately I'm not aware of any of these third party solutions. James Chong MCSE M+, S+, MCTS, Security+ msexchangetips.blogspot.com ftp://mail.msexchange911.net/ JP wrote: > Is there a way to set up the ability to add incoming email address to a > distribution list based on the subject line? You want a list server. Build your own: http://www.ftponline...

2003 FILTERING
I am using Outlook 2003 and have set the filtering to high so that only whitelist email is processed. I have my firebox, WatchGuard, set up to email me alerts. When I get the email, it comes in the format "firebox@ [xxx.xxx.xxx.xxx]". When I try to add to safe list, I get an error because it is not in the format user@domain.com. I would like to add this address to the safelist so my rules can move it to a specified folder. I tried adding as a contact and that still doesn't work. Any ideas? try words in header 'firebox@' - rules supersede the junk filter, so...

Auto Accept 2003- Delete Cancelled Meetings?
Is there a way to get autoaccept 2003 to actually delete cancelled meetings from the resource calendar instead of mark them as "deleted" but still leave them in the calendar, occupying free/busy. This is highly annoying! ...