How to calculate formula with data in file opened with GetOpenFilename?

Question

I wrote code to reformat a workbook by separating and combining information on separate sheets and then save every sheet separately as a CSV.

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

At the line:

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

I either get 1004 or 400 error.

I have the two pieces (opening a workbook, and reformatting) 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.

It didn't made a difference as the opened workbook is already activated. I tried to set the values for Bottom and Header.

Answer 1

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

Answer 2

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)