Excel 2010运行时错误'91'
我试图运行一个宏。我们将这个工作簿称为A.宏打开一个文件菜单来选择另一个工作簿,让我们称之为工作簿B.然后宏打开5个其他工作簿并将其他Excel文件中的信息拖入工作簿B.Excel 2010运行时错误'91'
这样工作只是以前的事了罚款2个月如果我有问题我不断收到Run-Time error '91'
的是
Cells.Find(What:=sFindString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
和
Cells.Find(What:=sFindString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
我的代码如下:
Public Sub Main(sPath, sFile)
Dim CR, LR As Integer, sAB_Bal, sABClos, sABComp, sABOpen, sAP_Bal, sAPClos, sAPComp, sAPOpen, sCol, sCol_OC, sFileBank, sFileGIT, sMD, sMN, sPathGIT As String
Dim FSO As Scripting.FileSystemObject, SourceFolder As Scripting.Folder, FileItem As Scripting.File
Application.DefaultFilePath = ActiveWorkbook.Path & "\" 'SET DEFAULT FILE PATH AND PROMPT TO OPEN THE GOAL AND INCENTIVE TRACKER FILE
Set FSO = New Scripting.FileSystemObject: Set SourceFolder = FSO.GetFolder(ActiveWorkbook.Path & "\")
sPathGIT = Application.GetOpenFilename("Worksheets (*.xlsx; *.xlsm; *.xlsb; *.xls), *.xlsx; *.xlsm; *.xlsb; *.xls", , "PLEASE SELECT THE G.I.T. FILE:")
If StrComp(sPathGIT, "False") = 0 Then
iYesNo = MsgBox("You clicked Cancel. Do you wish to exit?", vbYesNo, "EXIT?")
If iYesNo = 6 Then
GoTo Q1
ElseIf iYesNo = 7 Then
MsgBox ("You must re-open this file to be prompted for the G.I.T. file again."): Exit Sub
End If
End If
MonthPrompt.Show 'PROMPT FOR THE REPORT MONTH & YEAR
If MonthPrompt.ComboBox1.Value = vbNullString And MonthPrompt.ComboBox2.Value = vbNullString Then
Application.DisplayAlerts = False: Application.Quit
End If 'PROMPT FOR THE GIT FILE NAME & OPEN
Application.EnableCancelKey = xlDisabled 'PREVENT RANDOM LINE BY LINE VBA BREAK ISSUE
Application.ScreenUpdating = True: DoEvents: MsgBox ("First click OK to begin verifying each bank's file." & Chr(10) & Chr(10) & "(part 1 of 2...progress bar will display)")
ProgressBar.Caption = "PROCESSING - PLEASE WAIT...": DoEvents: ProgressBar.Percentage.Width = 0: Call ShowProgress(0.01): DoEvents
Workbooks.Open Filename:=sPathGIT: sFileGIT = Replace(sPathGIT, sPath, vbNullString)
sMN = MonthPrompt.ComboBox1.Value 'CURRENT MONTH NAME & DATE (ROW BELOW)
sMD = Month(MonthPrompt.ComboBox1.Value & "/1/" & MonthPrompt.ComboBox2.Value) & "/1/" & MonthPrompt.ComboBox2.Value
Sheets(8).Select: Call VerifyNoDup(sAB_Bal): sCol = CLet(sMD) 'Business DDA Average Balance
Sheets(5).Select: Call VerifyNoDup(sABClos): If sCol <> CLet(sMD) Then GoTo E2 'Business DDA Closed
Sheets(4).Select: Call VerifyNoDup(sABOpen): If sCol <> CLet(sMD) Then GoTo E2 'Business DDA Openings
Sheets(6).Select: Call VerifyNoDup(sABComp): sCol_OC = CLet(sMD) 'Business Open-Close
Sheets(7).Select: Call VerifyNoDup(sAP_Bal): If sCol <> CLet(sMD) Then GoTo E2 'Consumer DDA Average Balance
Sheets(2).Select: Call VerifyNoDup(sAPClos): If sCol <> CLet(sMD) Then GoTo E2 'Personal DDA Closed
Sheets(1).Select: Call VerifyNoDup(sAPOpen): If sCol <> CLet(sMD) Then GoTo E2 'Personal DDA Openings
Sheets(3).Select: Call VerifyNoDup(sAPComp): If sCol_OC <> CLet(sMD) Then GoTo E3 'Personal Open-Close
Windows(sFile).Activate: CR = 2: While StrComp(Range("C" & CR).Value, vbNullString) <> 0: CR = CR + 1: Wend: LR = CR: CR = 2
While CR < LR 'CONFIRM ALL BANK FILES ARE PRESENT
sFileBank = Range("C" & CR).Value
If sFileBank <> Range("C" & CR - 1).Value Then
If StrComp(Dir(sPath & sFileBank), vbNullString) = 0 Then GoTo E1
Workbooks.Open Filename:=sPath & sFileBank: Windows(sFile).Activate
End If: Call ShowProgress(CR/LR): DoEvents: CR = CR + 1
Wend: CR = 2
Application.ScreenUpdating = True: DoEvents: MsgBox ("Now click OK to begin copying data." & Chr(10) & Chr(10) & "(part 2 of 2...progress bar will display)")
ProgressBar.Percentage.Width = 0: Call ShowProgress(0.01): DoEvents
While CR < LR 'CONFIRM ALL BANK FILES HAVE REQUIRED WORKSHEETS AND FIELDS
Call ShowProgress(CR/LR): DoEvents
Call ScanImport(CR, LR, 0, sMN, sFileGIT, sFile, sABOpen, sCol, "raw data", "Business DDA")
Call ScanImport(CR, LR, 1, sMN, sFileGIT, sFile, sABClos, sCol, "raw data", "Business DDA")
Call ScanImport(CR, LR, 0, sMN, sFileGIT, sFile, sAB_Bal, sCol, "raw data 3", "Business DDA")
Call ScanImport(CR, LR, 0, sMN, sFileGIT, sFile, sABComp, sCol_OC, sCol, "Business DDA") 'FILL FORMULAS FOR BUSINESS OPEN-CLOSE
Call ScanImport(CR, LR, 0, sMN, sFileGIT, sFile, sAPOpen, sCol, "raw data", "Personal DDA")
Call ScanImport(CR, LR, 1, sMN, sFileGIT, sFile, sAPClos, sCol, "raw data", "Personal DDA")
Call ScanImport(CR, LR, 0, sMN, sFileGIT, sFile, sAP_Bal, sCol, "raw data 3", "Personal DDA")
Call ScanImport(CR, LR, 0, sMN, sFileGIT, sFile, sAPComp, sCol_OC, sCol, "Personal DDA") 'FILL FORMULAS FOR PERSONAL OPEN-CLOSE
Call ShowProgress(CR/LR): DoEvents: CR = CR + 1
Wend: Application.ScreenUpdating = True: Windows(sFile).Close: Application.DisplayAlerts = True: Exit Sub
E1: MsgBox ("You are missing the following required bank file:" & Chr(13) & Chr(13) & sFileBank): GoTo Q1
E2: MsgBox ("The column for month '" & sMD & "' appears inconsisent among the Business & Personal 'Open' or 'Closed' worksheets."): GoTo Q1
E3: MsgBox ("The column for month '" & sMD & "' appears inconsistent between the Business & Personal 'Open-Close' ratio worksheets."): GoTo Q1
Q1: Application.DisplayAlerts = False: Application.Quit
End Sub
Public Function CLet(sFindString) As String 'RETURN COLUMN LETTER
Cells.Find(What:=sFindString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
CLet = Mid(ActiveCell.Address, InStr(ActiveCell.Address, "$") + 1, InStr(2, ActiveCell.Address, "$") - 2)
End Function
Public Function RNum(sFindString) As Integer 'RETURN ROW NUMBER
Cells.Find(What:=sFindString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False).Activate
RNum = ActiveCell.Row
End Function
请尝试以下更改。
Public Function CLet(sFindString) As String 'RETURN COLUMN LETTER
Dim rng As Range
Set rng = Cells.Find(What:=sFindString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
If Not rng Is Nothing Then
rng.Activate
CLet = Mid(ActiveCell.Address, InStr(ActiveCell.Address, "$") + 1, InStr(2, ActiveCell.Address, "$") - 2)
End If
End Function
Public Function RNum(sFindString) As Integer 'RETURN ROW NUMBER
Dim rng As Range
Set rng = Cells.Find(What:=sFindString, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
If Not rng Is Nothing Then
rng.Activate
RNum = ActiveCell.Row
End If
End Function
非常感谢您的工作! – 2013-03-12 14:43:51
变量sFindString的值是什么? – evoandy 2013-03-08 16:05:08
该值在宏中选择...有一个月/年选择器..现在我选择2013年2月这将是2/1/2013 sFindString – 2013-03-08 16:07:39
据我所知91错误发生时,它不能找到您正在搜索的值。是'2/1/2013'肯定在你正在搜索的工作表中? – evoandy 2013-03-08 16:22:47