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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.
 
Upvote 0
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.
 
Upvote 0
Don't know how it worked previously...
It is missing an End IF statement after the End With Statement !
 
Upvote 0
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
 
Upvote 0
Try replacing all that code in post#5 with
VBA Code:
    Worksheets("Wires").AutoFilter.Range.Offset(1).Columns("T").Copy
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top