[RESOLVED] Run Time Error 1004 application defined object defined
I am using the following code to only display dates between a start date and end date
Code:
Public Sub MyFilter()
Dim lngStart As Long, lngEnd As Long
lngStart = Range("Hidden!O31").Value 'assume this is the start date
lngEnd = Range("Hidden!O32").Value 'assume this is the end date
Range("I15:ARR15").AutoFilter field:=1, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd
End Sub
Bu I am coming up with an error Run Time Error 1004 application defined object defined error, I think it may be because I am taking the start date and end date from another sheet but that would be just a guess any ideas
Last edited by Siddharth Rout; May 23rd, 2017 at 09:43 AM.
Reason: Added Code Tags
Re: Run Time Error 1004 application defined object defined
I tried your code.
errored initially when the cells were empty in the range you want to apply the filter.
Once I added headings into some of those fields it worked and continued working (even after deleting the cells and deleting the row).
Do you have data headings in the range you are specifying?
Can you get it to apply a filter only, then add in the dates?
Does your hidden sheet have correct dates in those two cells?
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
Re: Run Time Error 1004 application defined object defined
Hi Ecnic, To answer your questions I do have data headings in some of the range not all. Basically what I am trying to achieve is to just show dates >=startdate and <=enddate depending so the number of columns on show will be depending on the fill of those dates. I am trying anything to get it to work.
I just want to hide the irrelevant date columns and just show those depending on the start and end date. is what im doing even correct. This is an new area for me but I am trying.
Re: Run Time Error 1004 application defined object defined
It is very important that you declare your variables/objects and work with them Try this
Code:
Public Sub MyFilter()
Dim lngStart As Long, lngEnd As Long, lRow As Long
Dim rng As Range
Dim ws As Worksheet
With ThisWorkbook.Sheets("Hidden")
lngStart = .Range("O31").Value '<~~ Start date
lngEnd = .Range("O32").Value '<~~ End date
End With
Set ws = ThisWorkbook.Sheets("Whatever")
With ws
.AutoFilterMode = False
'~~> Get the last row
lRow = .Range("I" & .Rows.Count).End(xlUp).Row
Set rng = .Range("I15:ARR" & lRow)
With rng
.AutoFilter Field:=1, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd
End With
End With
End Sub
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Re: Run Time Error 1004 application defined object defined
Hi there and thanking you, I have updated the code as below; but it is not updating for me if the End date is changed, is there something I can do to have it updating if the end date is changing?
Public Sub MyFilter()
Dim lngStart As Long, lngEnd As Long, lRow As Long
Dim rng As Range
Dim ws As Worksheet
With ThisWorkbook.Sheets("Hidden")
lngStart = .Range("O31").Value '<~~ Start date
lngEnd = .Range("O32").Value '<~~ End date
End With
Set ws = ThisWorkbook.Sheets("3. Task Monitoring")
With ws
.AutoFilterMode = False
'~~> Get the last row
lRow = .Range("I" & .Rows.Count).End(xlUp).Row
Set rng = .Range("I1046:ARR" & lRow)
With rng
.AutoFilter Field:=1, _
Criteria1:=">=" & lngStart, _
Re: Run Time Error 1004 application defined object defined
I have the change associated to the sheet where the date can be entered. But the Macro is updating another sheet which is linked to the change. is this correct ??
Re: Run Time Error 1004 application defined object defined
[QUOTE=Siddharth Rout;5172203]Trish
When you are working with `Worksheet_Change` event, you have to be very careful. I have explained it here
I have read through your `Worksheet_Change` event and i think as you said i have named incorrectly i think. I have tried to upload the file and even zipped it is too large to upload.
So my best bet i think is to explain as clearly as i can what i am trying to achieve
The Following Macro is linked to my Sheet named 3. Task Monitoring
Sub Hide_columns()
Dim lngStart As Long, lngEnd As Long, c As Range, rng As Range, hiderng As Range
lngStart = Range("Hidden!O31").Value
lngEnd = Range("Hidden!O32").Value
Set hiderng = Range("I1046:AAP1046")
hiderng.EntireColumn.Hidden = False
For Each c In hiderng
If c.Value < lngStart Or c.Value > lngEnd Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(c, rng)
End If
End If
Next
If Not rng Is Nothing Then rng.EntireColumn.Hidden = True
End Sub
So this Macro is looking to a Sheet called Hidden and looking at the Start and End Date so when Cell O32 (the End Date), and changes the ranges withen the sheet called 3. Task Monitoring. so when and if Hidden!O32 is changed i want that macro to run, but at the moment it is not running at all even with the following Worksheet change linked to the Sheet named Hidden
Private Sub Worksheet_Change(ByVal Target As Range)
Stop
If Target.Address = "$O$32" Then
Call Module1.Hide_columns
End If
End Sub
Any Ideas with what i am doing incorrect.
Last edited by trish123; May 25th, 2017 at 03:34 PM.
Re: Run Time Error 1004 application defined object defined
Code:
Sub Hide_columns()
Dim lngStart As Long, lngEnd As Long, c As Range, rng As Range, hiderng As Range
lngStart = Range("Hidden!O31").Value
lngEnd = Range("Hidden!O32").Value
Set hiderng = Range("I1046:AAP1046")
hiderng.EntireColumn.Hidden = False
For Each c In hiderng
If c.Value < lngStart Or c.Value > lngEnd Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(c, rng)
End If
End If
Next
If Not rng Is Nothing Then rng.EntireColumn.Hidden = True
End Sub
I read this as :
- set vars
- show entire range
- loop the cells in the range
--- if the cell value is < lngstart (less than) OR the cell value > lngend (greater then)
---- add c to the (selected) rng
- if you have any cells selected as outside the start/end dates, hide the entire column
Only fault I can think of is that if there is one date outside your start/end anywhere in the column, the whole column is hidden.
Depends on your data of course...
Code seems ok though.
(PS use [ code ] tags around your code... helps when viewing
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
Re: Run Time Error 1004 application defined object defined
No Sorry afraid not, being honest i am only just a beginner at all this and am very enthusiastic to learn, i can see what it is doing alright but how am i to incorporate it into my existing one
Private Sub Worksheet_Change(ByVal Target As Range)
Stop
If Target.Address = "$O$32" Then
Call Module1.Hide_columns
End If
End Sub
Is this not supposed to run /refresh the Macro on update to the column at Hidden Sheet!O32
Re: Run Time Error 1004 application defined object defined
is cell O32 updated by the user? or some link, reference or formula
the worksheet change event may not fire from changes not made by user
did you use the drop down boxes at the top of the "Hidden" sheet code page to create the worksheet change event?
I have tried to upload the file and even zipped it is too large to upload.
can you make a copy, remove much of the data to reduce size, then zip and attach, leave all sheets and code
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Run Time Error 1004 application defined object defined
Well After alot of trouble shooting and moving about of my spreadsheet i seem to have well may have fixed my problem. I have to thank you all so much for patience and time taken to help me out.