473,376 Members | 1,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,376 software developers and data experts.

How to replace a criteria in AutoFilter by a variable?


I have this code:

Sub Macro4()
' Macro4 Macro

ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:="16/08/2008", _
End Sub

Instead of having to insert "16/08/2008" as Criteria1, I would like to have a variable in there corresponding to the maximum value of a column containing dates.

Could anyone help? That would be extremely helpful and very much appreciated...
Jul 29 '10 #1
9 9076
In this case the maximum would correspond to the most recent date in the DATE column....

01.01.2008 John SHOES 10 100
02.01.2008 John TROWSERS 20 110
03.01.2008 John SHIRTS 10 80
04.01.2008 John JEANS 25 90
05.01.2008 John SOCKS 20 95
06.01.2008 John SHOES 32 75
07.01.2008 John TROWSERS 45 65
08.01.2008 John SHIRTS 11 95
09.01.2008 John JEANS 12 12
10.01.2008 John SOCKS 14 13
11.01.2008 John SOCKS 10 100
12.01.2008 John SHOES 20 110
12.01.2008 John TROWSERS 10 80
12.01.2008 John SHIRTS 25 90
13.01.2008 John JEANS 20 95
14.01.2008 John SOCKS 32 75
15.01.2008 John SOCKS 45 65
16.01.2008 John SHOES 11 95
16.01.2008 John TROWSERS 12 12
16.01.2008 John SHIRTS 14 13
Jul 29 '10 #2
Guido Geurs
767 Expert 512MB
I hope this will help (see attachment):

Expand|Select|Wrap|Line Numbers
  1. Sub Macro4()
  2. Dim DateSelect As Date
  3.    DateSelect = Selection
  4.    Range("A1").Select
  5.    Selection.AutoFilter
  6.    ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:=Format(DateSelect, "dd.mm.yyyy"), _
  7.    Operator:=xlAnd
  8. End Sub
Jul 29 '10 #3
Thank you so much for your reply.

It says type mismatch regarding the DateSelect...
Jul 29 '10 #4
Guido Geurs
767 Expert 512MB
It's working on my PC.
I'm working with Excel 2003 ! You to ?
You must select a cell with a date like "G1" or "H1" !!!!
If You select a text cell than You will have an error.
Can You exclude the selection of a non date cell ?
I will try to find an solution.
If You can't solve it, is it possible to attach Your sheet ?
Jul 29 '10 #5
Guido Geurs
767 Expert 512MB
I think this will do it=

Expand|Select|Wrap|Line Numbers
  1. Sub Macro4()
  2. Dim DateSelect As Date
  3.    On Error GoTo Bad_Selection
  4.    DateSelect = Selection
  5.    Range("A1").Select
  6.    Selection.AutoFilter
  7.    ActiveSheet.Range("$A$1:$E$25").AutoFilter Field:=1, Criteria1:=Format(DateSelect, "dd.mm.yyyy"), _
  8.    Operator:=xlAnd
  9. Exit Sub
  10. Bad_Selection:
  11.    MsgBox "Select a date !"
  12. End Sub
Jul 29 '10 #6
Thank you very much for your reply

I think we are almost there... but I do have a problem with the date selection. In the file you attached ("How to....xls"), the dates are formatted as Text, therefore the filter works (Except for cell G1 which is formatted as Date, but the macro keeps functioning anyways).

My dates are formatted as dates and for some reason the macro doesn't recognise them as dates. So I tried to format them as Text as you did, but then values like 40380.... appear.

You will see a proof in the attachments: I copied identically your macro but because of my formatting of the date it won't work as it should.

Do you know what I can do about this?

Thank you so much.
Attached Files
File Type: zip Book11.zip (13.3 KB, 113 views)
Jul 29 '10 #7
Guido Geurs
767 Expert 512MB
After searching the web I have found out that autofiltering on dates isn't so easy.
What I have found is that autofilter uses the US format to filter on dates.
So I have changed the sheet dates (are = date AND time) to "mm/dd/jjjj" format and formated the var "DateSelect" to "mm/dd/yyyy".
And surprisingly it's working ! (see attachment)

Expand|Select|Wrap|Line Numbers
  1. Sub Macro5()
  2. Dim DateSelect As String
  3.    On Error GoTo Bad_Selection
  4.    If IsDate(Selection) Then
  5.       DateSelect = Format(Selection, "mm/dd/yyyy")
  6.       Range("A2").AutoFilter Field:=1, Criteria1:=DateSelect, Operator:=xlAnd
  7.    Else
  8.       GoTo Bad_Selection
  9.    End If
  10. Exit Sub
  11. Bad_Selection:
  12.    MsgBox "Select a date !"
  13. End Sub
Attached Files
File Type: zip Book11_v3.zip (14.9 KB, 98 views)
Jul 30 '10 #8
Guido Geurs
767 Expert 512MB
If the user want to see the date in an other format (not "mm/dd/yyyy") than You can let the 1e col in the users format and hide a col with the reference to col A but with the US format and filter on that col (col "F" in the attachment).
Attached Files
File Type: zip Book11_v4.zip (16.4 KB, 151 views)
Jul 30 '10 #9
Thank you sooo much for your help, it is immesnily appreciated
Jul 30 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Ones Self | last post by:
Hi all: I'm trying to replace using a regexp read from a file: $string = '123 456 789'; # these two are usualy read from a file, # and so have to be in variables. $re = '()'; $rep = '|$1|';
by: BBM | last post by:
This is so bizarre I hesitate to post it, but I need to get this working. My code looks like this... Private Const cmdTestResourcesSel = "SELECT * FROM TResources" & _ " WHERE Scenario =...
by: Hank Rouse | last post by:
Looking for a tool, or code that does the following. Edit in TBL1, if meets criteria in TBL2, COL1. TBL2, COL2 has the REPLACE WITH information. Not sure if this edited data can be written...
by: gabriel.becedillas | last post by:
I have a module that defines a variable with a constant value and now I need to make that value dynamic, without affecting module clients. In other words, I need to call a function witout using...
by: Svein Erik | last post by:
This is a C# 2005 project: I'm struggling to figure how to make a variable accessible and changeable in several forms in a windows application.. It's only a int variable which is set when the...
by: doug9149 | last post by:
Hello all, I've got some code I wrote using VBA for Excel that I'm trying to recreate using C#.NET. The code autofilters 1 entire column from 1 Worksheet from a Workbook and then appends this...
by: n.torrey.pines | last post by:
Are global variables (and const's) guaranteed to be initialized before static class members (and methods) ? const int x = 19907; int get_x() { return x; } // another compilation unit: ...
by: angelajean | last post by:
Hi all, I have a problem here that I am not sure how I should go about solving. I am thinking of creating classes that iteratively calls on other classes such that at the end of the day, I can get...
by: masterofzen | last post by:
This seems like such an easy one, but I can't seem to figure it out or find any answers. I think I might be doing something wrong besides the obvious. I'm trying to replace a particular variable...
by: yash0101 | last post by:
I have a hash , and in the value part of that (which is a string , So that variable interpolation can happen) i have used a variable . Now later in the program i set this variable and try to...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.