如何计算与GetOpenFilename打开的文件中的数据公式?

Question

我编写了一段代码，通过在单独的工作表上分离和组合信息来重新格式化工作簿，然后将每个工作表单独保存为CSV。

我的代码的开头:

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和Header分别用于查找范围的标题和最后一行。在我的代码中，我在单独的页上使用了很多次。

当我从需要修改的文件中运行它时，代码可以工作。但我需要将其分配给另一个电子表格的按钮，通过VBA打开待修改的文件，然后应用代码。所以我添加了这个:

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

在行:

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

我得到1004或400错误。

我有两个部分(打开工作簿和重新格式化)分别工作，但我不能合并它们。

在使用它们之前，我指定了两个需要使用的整数。我尝试了包括NewBatch.Activate在内的多个更改。

由于打开的工作簿已被激活，因此没有影响。我尝试设置底部和页眉的值。

Answer 1

可能是这样的:

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

通过定义工作表和引用而不是依赖于选择或活动工作表，我发现了更可靠的性能。尝试在range()和cells()引用之前定义正在执行此行并引用的工作表，看看是否有帮助。

设置为工作表Set ws = Sheets("OUTGOING CHECKS")

Bottom = WorksheetFunction.Match((ws.Cells(Rows.))统计,1)指标(xlUp)),最终ws.Range("A: A"), 0)