Autofilter And Dates Added Via Macros
Jun 16, 2014
The problem is that when I add dates via macros (like from a userform to a selected cell) to a cell in autofiltered column, the autofilter doesn't recognize these values as "proper" dates. If I activate the formula bar and press Enter, then the autofilter identifies the date properly.
This is how the autofilter popup looks like after I have added a date via my macro: [URL] ....
The new date doesn't get categorized by year and month, instead it just shows as text on a separate line (circled in red) (huhtikuu = April)
I have earlier used Formula:
[Code] ....
How I could get the autofilter working right with the inputed values? I have tried adding the date via .Value and .Formula but neither one works.
View 2 Replies
ADVERTISEMENT
Mar 12, 2014
So I have a column, say column A, that I have a auto filter assigned. I would like it to auto-refresh the filter every time I add in data at the end.
I found this macro, but it only works for changes made within the filter, not if I add in data at the end. [URL]
Private Sub Worksheet_Calculate()
If Me.FilterMode = True Then
With Application
.EnableEvents = False
[Code].....
View 6 Replies
View Related
Mar 3, 2003
= LOOKUP(BH23,B1:AF1) this is looking for a date (a returned value from another set of equations) in a row of other dates. I want a macro to use this lookup, find the date and then select it as an auto filter, with criteria? here is the macro and the part where it says auto filter is the part where I want it to do the above lookup function
Sub Brooke()
ActiveWindow.ScrollWorkbookTabs Sheets:=-4
Sheets("Feb 03").Select
Selection.AutoFilter Field:=28, Criteria1:="BH"
Range("AD7:AD56").Select
Selection.Copy
Sheets("Brooke Hotel Running Order").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Feb 03").Select
Range("AD62:AD68").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Brooke Hotel Running Order").Select
Range("B32").Select
ActiveSheet.Paste
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Range("F23").Select
End Sub
View 3 Replies
View Related
Dec 22, 2008
How can I create a custom autofilter that will show me dates within 30 days of today in Excel 2003?
View 3 Replies
View Related
Jul 18, 2009
In my 2003 workbook I have a number of sheets.
The 'Details' sheet has, among other data, the 'year' that the workbook refers to. From this, the beginning and end dates of each quarter appear in Cells F2:F10 in the 'Details' Sheet in the format dd/mm/yyyy.
In the 'Management' Sheet I have four buttons - Q1, Q2, Q3 and Q4. I want to attach code to apply a custom autofilter to each button so that clicking on one of the buttons will show only data from that quarter.
Using the macro recorder the following code is generated when applying a custom AutoFilter for Quarter One:
Sub QuarterOne
Selection.AutoFilter Field:=3, Criteria1:=">=01/01/2009", Operator:=xlAnd , Criteria2:="
View 9 Replies
View Related
Aug 17, 2006
I have this script it does exactly as i want it to do, filter a table of date using two selected date in D3 and D4. There is only one problem: when i choose the dates and run the script it changes them around it around.
e.g
cell D3 = 01/11/2006
cell D4 = 01/12/2006
so it should just show all of november (11)
but D3 will filter using 11/01/2006
and D4 will use 12/01/2006
Sub DateFilter()
Range("a8:r323").AutoFilter Field:=1, Criteria1:=">=" & Range("D3").Value, Operator:=xlAnd, _
Criteria2:="<=" & Range("D4").Value
End Sub
View 7 Replies
View Related
Jan 9, 2007
I recorded a macro to custom AutoFilter 2 columns in a worksheet. The information will be filtered between 2 dates; i.e. >= cell B2 and <= cell C2.
Both cells are formated to dd/mm/yy so i entered 1/12/06 (for 1st Dec) in cell B2 and 31/12/06 (for 31st Dec) in cell C2.
When i enter these 2 dates, click the button to run the macro and check the custom filter in the "Planning" worksheet it appears that the dates change to 12/01/06 and 21/12/06 and no records appear - although i know at least 65 rows should show.
If i then enter the dates in B2 and C2 as mm/dd/yy instead (but leave the date format of the cell as dd/mm/yy) the filter works perfectly; i.e. enter 12/01/06 and 12/31/06. However I plan to issue this spreadsheet out to other staff and as every other date in the spreadsheet is in the uk format dd/mm/yy i dont want to confuse them by insisting they use the mm/dd/yy format for this one function.
Has anyone ever encountered this before? and if so is there some way i can fix it?
Heres the code for the filter:
Sub Monthly_Stats()
Sheets("Planning").Select
Selection.AutoFilter Field:=5, Criteria1:=">=" & Sheets("Filtered Statistics").Range("B2").Value, Operator:=xlAnd _
, Criteria2:="<=" & Sheets("Filtered Statistics").Range("C2").Value, Operator:=xlAnd
Selection.AutoFilter Field:=82, Criteria1:="<>"
End Sub
View 3 Replies
View Related
Nov 24, 2007
I am encounting problems with an AutoFilter which is run in a macro after the user has selected dates from 2 Date Pickers (DTPicker) on a user form. The filter is applied to 1 column in a spreadsheet containing dates in the formay "dd/mm/yy" and is supposed to filter dates =2 and between a range of dates as selected. The resultant filter is blank, if the macro is debugged at this point the date format for the 1st criteria is found to have swopped the "dd" & "mm" around but the 2nd criteria is fine. eg. user selected 01/07/07 and 31/07/07 the resultant filter applied is 07/01/07 and 31/07/07. I have re-formatted dates to Long as per the article on this site @ http://www.ozgrid.com/VBA/autofilter-vba-dates.htm but still get the same result ???
Dim fDate, tDate As Date
Dim lfDate, ltDate As Long
fDate = frmReportMenu.dtpFromDate.Value
tDate = frmReportMenu.dtpToDate.Value
fDate = DateSerial(Year(fDate), Month(fDate), Day(fDate))
tDate = DateSerial(Year(tDate), Month(tDate), Day(tDate))
lfDate = fDate
ltDate = tDate
View 2 Replies
View Related
Feb 15, 2010
I have a column of dates: 1 day per row: 01/01/10, 02/01/10, 03/01/10, etc in sequence, without gaps (European date format). The list can begin with a different start date depending on initial user input and is not always a Monday. I want the user to be able to autofilter so that it only shows dates that fall on a Monday.
Private Sub Worksheet_Activate()
Dim dDate As Date
Dim strDate As String
'disable autofilter if already enabled
With ActiveSheet
.AutoFilterMode = False
End With
'autofilter column a only, from a12, to filter Mondays
If IsDate( Range("a12")) Then
dDate = Range("a12")
strDate = Weekday(dDate)
Range("a12").AutoFilter
Range("a12").AutoFilter field:=1, Criteria1:=strDate
End If
End Sub
View 2 Replies
View Related
Apr 30, 2014
I am having trouble with the following macro,
[Code] .....
When I press F8 and step through the macro, the StartDate and EndDate both seem to populate correctly.
The macro seems to go through the motions but returns NO results, unlike the recorded macro.
Macro code to "Clear Filter".
View 2 Replies
View Related
Aug 15, 2013
Through VBA I am trying to filter for today's date and all dates 30 days in the future as well as all dates 30 days in the past.
I am currently using the code below, but it only show dates 30 days in the future. I cannot get it to populate cells that are either 30 days in the future or 30 days in the past.
Rows("4:4").Select
Selection.AutoFilter
ActiveSheet.Range("$A$4:$HQ$1000").AutoFilter Field:=4, Criteria1:="TBD"
ActiveSheet.Range("$A$4:$FQ$1000").AutoFilter Field:=12, Criteria1:= _
">=" & Date, Operator:=xlAnd, Criteria2:="
View 2 Replies
View Related
Jan 14, 2007
I have a workbook with 2 sheets I want to make an autofilter by two method :
- select case statement
- two dates
View 4 Replies
View Related
Feb 11, 2008
i wanted to create a macro that will autofilter my starting dates in I to the newest date entered. So ive recorded the macro: ....
View 9 Replies
View Related
Jun 27, 2013
We have created a macro that basically looks for rows that contain an "H" and hides the row if it does.
Users can add new rows throughtout the year to this spreadsheet. and based on certain criteria, an H or U will be placed in a hidden column which the macro looks at and hides any row it finds an H.
The user has to click on the button that has the macro assigned to it once they have finished working on the spreadsheet.
The problem we're finding is that for users who insert/delete rows, once they click the button it takes up to 15 seconds to run through macro (which is ok). However, users who haven't added or deleted any rows and who click the button, they have to wait upto 5 minutes (which isn't ok) for the macro ro run.
We can't figure out why the macro takes longer to run when no changes have been made?
View 8 Replies
View Related
Sep 25, 2009
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Aug 20, 2014
I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
View 4 Replies
View Related
Jul 28, 2008
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
View 9 Replies
View Related
Jul 23, 2005
What is the commands or script for deleting a macro automatically using
another workbook macro.
View 9 Replies
View Related
Jul 14, 2008
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies
View Related
Jan 16, 2007
I can set the autofilter, change it, remove all using Macros. What I need to know how to do is reset a filtered column to ALL while leaving all other filters on.
View 2 Replies
View Related
Feb 13, 2006
I have to add up how many A's, S's, ST's and L's I have in a column how do I
do that?
Column 1Column 2
AA
A
AA
STA
AA
AA
AA
A
A
AA
AA
AA
AA
A
LL
AA
AA
A
AA
SS
SS
AA
View 9 Replies
View Related
Apr 28, 2008
How to add cells where some of the cells have the value #N/A?
View 9 Replies
View Related
Dec 14, 2009
I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
View 11 Replies
View Related
Apr 13, 2007
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
View 2 Replies
View Related
Apr 11, 2014
I need to create a Spreadsheet where a certain Value is added to the from of the data I enter into the cell. For example: All of my MAC Addresses start with 00:80:64. I want to be able to just type in the last three entries into the cell and have Excell automatically add the value 00:80:64 So I want to be able to add a specified value to ebvery entry in a collumn.
View 1 Replies
View Related
Sep 23, 2009
Our system, when displaying times adds a space before the time, space05:14:15 - space14:56:39, When I use this formula, =(F292-E292+(F292<E292))*24-0.5, to figure out hours worked, it does not work. If I remove the space, the formula works fine. Is there an easy way to automatically remove the space instead of having to do it manually?
View 11 Replies
View Related
Mar 24, 2008
I want to create a code that adds a check box for each sheet that's added and takes the name of the sheet as the Caption.
View 10 Replies
View Related
Apr 30, 2009
I finally got the perfect code that works for me, to list a folder with path name.
if I select any root like C:, D:, E:, an extra backslash is added. can this be fixed that, what ever folder or root drive I select only 1 backslash is added. here is the code
View 2 Replies
View Related
Dec 28, 2009
I have a workbook that has a dynamic number of tabs. Every day the report is updated, it will create a tab for yesterday's date. I need to somehow create a summary page that will add the most current tab name (which is yesterday's date) to column B and several cells from row 7 to the appropriate row on the summary page each time I add a new tab. I have attached the spreadsheet in question. I have added manual references to the fields I need to use in the summary on the tab MTD (2) If I am able to get this to work it will replace MTD.
View 8 Replies
View Related
