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.

You should aim to never use `Range()`, `Cells()` etc without specifying a worksheet for context, otherwise both of those (in a regular code module) will refer to the `ActiveSheet` — Tim Williams, Nov 17 '22 at 16:57
@TimWilliams, I totally agree. But this is not what is causing the error in this case. Right? — Rick, Nov 17 '22 at 22:39
`Bottom = WorksheetFunction.Match((Cells(Rows.Count, 1).End(xlUp)), Range("A:A"), 0)` is finding the row of the first cell in Col A which has the value in the last-occupied cell in Col A. Is that the intent? Whenever you have multiple workbooks open it's best to always use `wbVar.Sheets()` instead of just `Sheets()` so it's clear what's going on in your code without having to scan for the most-recent Activate/Select statement to get the context. — Tim Williams, Nov 17 '22 at 22:54
@TimWilliams yes, that's the intent to find the number of the last row where Column A has values. I never used `wbVar` before. Can you please clarify how to use it to fix my error. I tried to used in replacement for `Sheets` but I got a 424 object required code. Do I have to define it first? — Rick, Nov 17 '22 at 23:08
The line I quoted doesn't find the last-occupied row though, unless values in ColA are all unique (eg. if last used cell A500 has "hello" and "hello" also occurs in A10 then `Bottom` is 10 not 500) `Cells(Rows.Count, 1).End(xlUp).Row` would be the last occupied row in ColA. — Tim Williams, Nov 17 '22 at 23:44
For some reason `Cells(Rows.Count, 1).End(xlUp).Row` always returned 1! I also can't use `WorksheetFunction.Match` now because every time I use it with `Application.GetOpenFilename` I get 1004, so I just use `ws.Range("A65536").End(xlUp).Row` I think my main problem now is to get `WorksheetFunction.Match` to work with `Application.GetOpenFilename`. I don't understand what is the problem of having both in one code. Each works perfectly on its own. — Rick, Nov 18 '22 at 00:18

score 1 · Accepted Answer · answered Nov 18 '22 at 00:02

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

score 0 · Answer 2 · answered Nov 17 '22 at 20:14

0

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)

answered Nov 17 '22 at 20:14

SaintSnowmad

11
2

Thank you. But I still get an "Application-defined or object-defined" error. Any suggestions? – Rick Nov 17 '22 at 22:20

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

2 Answers2