VBA code not working after adding GetOpenFilename-CodePudding

I need help figuring out what is going wrong. I wrote a long code to basically reformat a workbook by separating and combining information on separate sheets and then save every sheet separately as a CSV. Here is the beginning of my code:

Sub All()
Dim Bottom As Long
Dim Header As Long

> 'A. CHECK DATE

If ThisWorkbook.Sheets("ACH PULL").Range("C1") <> Date Then
MsgBox "ERROR" & Chr(10) & "Date on file is different than today's date" & Chr(13) & "Ask client for corrected file"
Exit Sub
Else

> '1. OUTGOING CHECKS

Sheets("OUTGOING CHECKS").Select
Bottom = WorksheetFunction.Match((Cells(Rows.Count, 1).End(xlUp)), Range("A:A"), 0)
Header = WorksheetFunction.Match("Account*", Range("A:A"), 0)
If Bottom <> Header Then
MsgBox "ERROR" & Chr(10) & "The batch contains outgoing checks" & Chr(13) & "Ask client for corrected file"
Exit Sub

Bottom and Header are used to find the header of the range and the last row respectively. I use this so many times in my code on separate sheets.

The code works great when I run it from the file that I need to modify. But I need to assign it to a button to another spreadsheet to open the to-be-modified file through VBA and then apply the code. So I added this:

Sub All()
Dim FileToOpen As Variant
Dim NewBatch As Workbook
Dim Bottom As Integer
Dim Header As Integer




FileToOpen = Application.GetOpenFilename(Title:="Find batch file")
If FileToOpen <> False Then
Set NewBatch = Application.Workbooks.Open(FileToOpen)
End If



'A. CHECK DATE

If Sheets("ACH PULL").Range("C1") <> Date Then
MsgBox "ERROR" & Chr(10) & "Date on file is different than today's date" & Chr(13) & "Ask client for corrected file"
Exit Sub
Else

'1. OUTGOING CHECKS



Sheets("OUTGOING CHECKS").Select

Bottom = WorksheetFunction.Match((Cells(Rows.Count, 1).End(xlUp)), Range("A:A"), 0)
Header = WorksheetFunction.Match("Account*", Range("A:A"), 0)
End If
If Bottom <> Header Then
MsgBox "ERROR" & Chr(10) & "The batch contains outgoing checks" & Chr(13) & "Ask client for corrected file"
Exit Sub
.. The rest of the code

Now, when I try to run this, it goes well until the line:

Bottom = WorksheetFunction.Match((Cells(Rows.Count, 1).End(xlUp)), Range("A:A"), 0)

I either get 1004 or 400 error. I Dim'd the two integers that I need to use, I tried to make multiple changes including Activating the workbook but still not sure what is causing the problem in this part in the code.

I have the two pieces (VBA opening a workbook, and the reformatting code) working separately, but I can't combine them!

I Dim'd the two integers that I need to use before using them. I tried making multiple changes including

NewBatch.Activate

But it didn't actually made a difference as the opened workbook is already activated. I tried to set the values for Bottom and Header but that didn't work too.

Thanks!

CodePudding user response：

Something like this maybe:

Sub All()
    
    Dim FileToOpen As Variant
    Dim NewBatch As Workbook
    Dim Bottom As Long, Header As Variant 'not Long
    
    FileToOpen = Application.GetOpenFilename(Title:="Find batch file")
    If FileToOpen = False Then Exit Sub 'user cancelled open
    
    Set NewBatch = Application.Workbooks.Open(FileToOpen)
    
    'A. CHECK DATE
    If NewBatch.Sheets("ACH PULL").Range("C1").Value <> Date Then
        ProblemMsg "Date on file is different than today's date." & _
                    vbLf & "Ask client for corrected file"
        Exit Sub
    End If
    
    '1. OUTGOING CHECKS
    With NewBatch.Sheets("OUTGOING CHECKS")
        Bottom = .Cells(.Rows.Count, 1).End(xlUp).Row 'last entry in Col A
        Header = Application.Match("Account*", .Range("A:A"), 0) 'not WorksheetFunction.Match

        If IsError(Header) Then 'make sure we located "Account*"
            ProblemMsg "'Account*' not found in ColA on sheet '" & .Name & "'"
        Else
            If Bottom <> Header Then
                ProblemMsg "The batch contains outgoing checks." & vbLf & _
                           "Ask client for corrected file."
                Exit Sub
            End If
        End If
    End With
    
    '...
    '...
End Sub

'Utility sub for displaying error messages
Sub ProblemMsg(msg As String)
    MsgBox "ERROR" & vbLf & msg, vbExclamation, "Please review"
End Sub

CodePudding user response：

I have found more reliable performance by defining worksheets and referencing rather than relying on selection or active sheet. Try defining the worksheet this line is being performed on and referencing before the range() and cells() references and see if that helps.

Dim ws as Worksheet Set ws = Sheets("OUTGOING CHECKS")

Bottom = WorksheetFunction.Match((ws.Cells(Rows.Count, 1).End(xlUp)), ws.Range("A:A"), 0)