VBA Macro was working, now getting a 1004 error

bjohnson2235

New Member
Joined
Jun 16, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have a macro that worked fine for a month and a half, but is now giving me a 1004 Runtime error "Application-defined or object defined error". Below is the code in which the macro is errors out on.

The gist of the macro is to filter by a date provided by the user, then filter by a fixed column on that date. Where the error then occurs is when the macro goes to a column "T", and tries to find the first visible row. The code is in a form, and works well on one option of the form, but not this option.

VBA Code:
'Gets the date
    Dim Start_date As Integer
    Dim UserEntry As String
    Dim Msg As String
    Dim TheDate As String
    Msg = "Enter Wire Journal date for processing (MM/DD/YYYY)"
    
        Do
            UserEntry = InputBox(Msg)
         If UserEntry = "" Then Exit Sub
         If IsDate(UserEntry) Then
            ActiveSheet.Range("AI1").Value = Format(UserEntry, "mm/dd/yyyy")
        Else
            Msg = "Please try again.  Enter date as mm/dd/yyyy"
      End If
      Exit Do
      Loop
      
    'Filters by the date entered in AI1, filters by deposit date
    ActiveSheet.Range("$A$1:$AA$1").End(xlDown).AutoFilter Field:=26, Criteria1:= _
    "=" & Range("AI1"), Operator:=xlAnd
    
    'Filters by Receipt Applied
    Sheets("Wires").Select
    Dim A As Range, Z As Integer
    Set A = Range(Range("$A$1:$AA$1"), Range("$A$1:$AA$1").End(xlDown))
    A.AutoFilter Field:=13, Criteria1:="Receipt Applied"
    Z = WorksheetFunction.Count(A.Cells.SpecialCells(xlCellTypeVisible))
    If Z = 0 Then
    MsgBox "There are no items with a status of Receipt Applied. Update the Wires tab and re-run the Macro"
    
    Else
      
    'Finds the first visible row in Column T for Receivable ID
    
    Sheets("Wires").Select
    With Worksheets("Wires").AutoFilter.Range
       [COLOR=rgb(184, 49, 47)] Range("T" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select    ' This is the row that causes the 1004 error[/COLOR]
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
122
Office Version
  1. 365
  2. 2019
Have you used the VBA Debug option to step into and thru the code, one line at a time, to determine which line of code throws the error.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,194
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Have you used the VBA Debug option to step into and thru the code, one line at a time, to determine which line of code throws the error.

@RayFrye It is the middle line below that @bjohnson2235 has tried to manually format in the code (and put a comment at the end ;) )

VBA Code:
    With Worksheets("Wires").AutoFilter.Range
       [COLOR=rgb(184, 49, 47)] Range("T" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select    ' This is the row that causes the 1004 error[/COLOR]
    End With

@bjohnson2235 if you want to manually format code when posting you need to use the RICH tag option rather than the VBA tag option.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,898
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Don't know how it worked previously...
It is missing an End IF statement after the End With Statement !
 

bjohnson2235

New Member
Joined
Jun 16, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

The error is highlighted below in red (correctly tagged, thanks Mark858). The end if statement is much lower in the code, but the macro errors out prior to getting to the end if statement. I did not add all of the code since it is much longer. I only originally added the code where it starts to where the macro errors out with the 1004 error.

I believe the error is due to the offset, because if I remove the offset, then the error goes away. The issue is that I need the offset to copy the correct information.

Rich (BB code):
    With Worksheets("Wires").AutoFilter.Range
       Range("T" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
    End With
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Try replacing all that code in post#5 with
VBA Code:
    Worksheets("Wires").AutoFilter.Range.Offset(1).Columns("T").Copy
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,306
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
    Z = WorksheetFunction.Count(A.Cells.SpecialCells(xlCellTypeVisible))
    If Z = 0 Then

I think Z is never 0, because at least the header always visible, so try:
VBA Code:
    Z = WorksheetFunction.Count(A.Cells.SpecialCells(xlCellTypeVisible))
    If Z = 1 Then
 

Forum statistics

Threads
1,141,412
Messages
5,706,297
Members
421,439
Latest member
JordsdoExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top