Results 1 to 23 of 23

Thread: [RESOLVED] Run Time Error 1004 application defined object defined

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    Resolved [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

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    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?

    BOFH Now, BOFH Past, Information on duplicates

    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...

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    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.

  4. #4
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    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

  5. #5

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    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, _

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run Time Error 1004 application defined object defined

    use a worksheet_change event to respond to a change in cell O32, something like:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$O$32" Then
            Call Module1.myFilter
        End If
    End Sub

  7. #7

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    Re: Run Time Error 1004 application defined object defined

    I have entered the Worksheet_change as above but still when that cell changes it still does not call the macro to update??

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run Time Error 1004 application defined object defined

    If you do this:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
           stop
        If Target.Address = "$O$32" Then
            Call Module1.myFilter
        End If
    End Sub
    Does it stop there?

  9. #9

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    Re: Run Time Error 1004 application defined object defined

    Nope still not calling macro on the Cell change.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run Time Error 1004 application defined object defined

    If it's not stopping, I'd say you have the change event tied to the wrong worksheet maybe.

  11. #11

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    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 ??

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run Time Error 1004 application defined object defined

    Can you zip and attach the workbook?

  13. #13
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Run Time Error 1004 application defined object defined

    Trish

    When you are working with `Worksheet_Change` event, you have to be very careful. I have explained it here

    Worksheet_Change

    Also if you have correctly defined your objects and variables then you will not have any problems in getting the code work

    If you wish, you can zip and attach a workbook. Also explain in clear word what exactly is it that you are trying to achieve.
    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

  14. #14

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    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.

  15. #15
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run Time Error 1004 application defined object defined

    Nothing in that code indicates that you have anything tied to a worksheet change event.

    See attached. Change the value in O32.
    Attached Files Attached Files

  16. #16

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    Re: Run Time Error 1004 application defined object defined

    Apologies i have updated my reply above

  17. #17
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run Time Error 1004 application defined object defined

    So, out of curiosity, is "Hidden" actually HIDDEN? If so, how can a user change anything on that sheet?

  18. #18

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    Re: Run Time Error 1004 application defined object defined

    No Sorry it is just the name of the sheet. Sorry my error bad name granted

  19. #19
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Run Time Error 1004 application defined object defined

    Did my example in #15 help at all?

  20. #20
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    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

    BOFH Now, BOFH Past, Information on duplicates

    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...

  21. #21

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    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

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  23. #23

    Thread Starter
    New Member
    Join Date
    May 2017
    Location
    Vancouver, Canada
    Posts
    11

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width