我在其他一些帖子上看到了這個錯誤訊息，但沒有一個真正符合我的情況。
我有一些自動過濾功能一個接一個地完成，它檢查目標單元格是否為空，如果是則繼續。
代碼運行良好，直到第 6 次迭代出現錯誤“呼叫的物件已與其客戶端斷開連接”。
我不確定是什么導致了這個問題，任何幫助將不勝感激！
Option Explicit
Private Sub CommandButton49_Click()
'
Dim Wsdnd As Worksheet
Set Wsdnd = Sheets("DO NOT DELETE")
Dim A0, A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, ... (more variables) As Range
Set A3 = Wsdnd.Range("BD3")
Set A4 = Wsdnd.Range("BD4")
Set A5 = Wsdnd.Range("BD5")
Set A6 = Wsdnd.Range("BD6")
Set A7 = Wsdnd.Range("BD7")
Set A8 = Wsdnd.Range("BD8")
... (More variables)
'
Wsdnd.Range("BC3:BE50").Calculate 'Refreshing "Category", "Apply All Data", "Match Lookup Value" Lists on DO NOT DELETE sheet
Application.Calculation = xlManual 'Restarts manual calculations only for workbook speed
'
'Filter #4
If Not IsEmpty(A6.Value) Then
Sheets("Database").Range("B$24:BL$71499").AutoFilter Field:=WorksheetFunction.Match(Sheets("DO NOT DELETE").Range("BE6"), _
Worksheets("Database").Range("B23:BL23"), 0), Criteria1:=A6.Value, Operator:=xlFilterValues
Else
End If
'
'Filter #5
If Not IsEmpty(A7.Value) Then
Sheets("Database").Range("B$24:BL$71499").AutoFilter Field:=WorksheetFunction.Match(Sheets("DO NOT DELETE").Range("BE7"), _
Worksheets("Database").Range("B23:BL23"), 0), Criteria1:=A7.Value, Operator:=xlFilterValues
Else
End If
'
'Filter #6
If Not IsEmpty(A8.Value) Then
Sheets("Database").Range("B$24:BL$71499").AutoFilter Field:=WorksheetFunction.Match(Sheets("DO NOT DELETE").Range("BE8"), _
Worksheets("Database").Range("B23:BL23"), 0), Criteria1:=A8.Value, Operator:=xlFilterValues
Else
End If
'
'... Filters continue
變數中呼叫的資料是 String ("F8442") String ("Unspecified") Integer ("345")
我不確定上面的資料型別是否會成為問題，因為它們指的是一個單元格，并且很多時候單元格值可以從文本更改為數字，反之亦然。
編輯：我遺漏了 Range.Calculate 和 Application.Calculation = xlManual，因為我認為它們不會對代碼產生影響，但在此處添加了它們，因為我真的不確定。但是，當我從代碼中洗掉它時，它仍然在同一位置給出相同的錯誤。
uj5u.com熱心網友回復：
好吧，問題似乎是雙重的，一個是 Criteria 需要是一個字串，并且它正在回傳一個整數，其次是 Operator:=xlFilterValues 被放置在只有一個變數的指令之后，這被 excel 眾神看得很差。
至少在我的情況下，這不再是問題了。希望這些資訊在未來對其他人有所幫助！
修改后的代碼：
Option Explicit
Private Sub CommandButton49_Click()
'
Dim Wsdnd As Worksheet
Set Wsdnd = Sheets("DO NOT DELETE")
Dim A0, A1, A2, A3, A4, A5, A6, A7, A8, A9, A10, ... (more variables) As Range
A4 = Wsdnd.Range("BD4").Value
A5 = Wsdnd.Range("BD5").Value
A6 = Wsdnd.Range("BD6").Value
A7 = Wsdnd.Range("BD7").Value
A8 = Wsdnd.Range("BD8").Value
... (More variables)
'
Wsdnd.Range("BC3:BE50").Calculate 'Refreshing "Category", "Apply All Data", "Match Lookup Value" Lists on DO NOT DELETE sheet
Application.Calculation = xlManual 'Restarts manual calculations only for workbook speed
'
'Filter #4
If Not IsEmpty(A6) Then
Sheets("Database").Range("B$24:BL$71499").AutoFilter Field:=WorksheetFunction.Match(Sheets("DO NOT DELETE").Range("BE6"), _
Worksheets("Database").Range("B23:BL23"), 0), Criteria1:=A6
Else
End If
'
'Filter #5
If Not IsEmpty(A7) Then
Sheets("Database").Range("B$24:BL$71499").AutoFilter Field:=WorksheetFunction.Match(Sheets("DO NOT DELETE").Range("BE7"), _
Worksheets("Database").Range("B23:BL23"), 0), Criteria1:=A7
Else
End If
'
'Filter #6
If Not IsEmpty(A8) Then
Sheets("Database").Range("B$24:BL$71499").AutoFilter Field:=WorksheetFunction.Match(Sheets("DO NOT DELETE").Range("BE8"), _
Worksheets("Database").Range("B23:BL23"), 0), Criteria1:=A8
Else
End If
uj5u.com熱心網友回復：
過濾作業表
Option Explicit
Private Sub CommandButton49_Click()
FilterWorksheet
End Sub
Sub FilterWorksheet()
Const ProcName As String = "FilterWorksheet"
On Error GoTo ClearError
Const sName As String = "DO NOT DELETE"
Const srgAddress As String = "BC3:BE50" ' ?
Const srrgAddress As String = "BD4:BD10" ' resize
Const scOffset As Long = 1 ' ('BE4:BE10')
Const dName As String = "Database"
Const drgAddress As String = "B23:BL71499"
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
Dim srg As Range: Set srg = sws.Range(srgAddress)
Dim srrg As Range: Set srrg = sws.Range(srrgAddress)
Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
If dws.AutoFilterMode Then dws.AutoFilterMode = False
Dim drg As Range: Set drg = dws.Range(drgAddress)
Dim dhrg As Range: Set dhrg = drg.Rows(1)
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
srg.Calculate 'Refreshing "Category", "Apply All Data", "Match Lookup Value"
Dim sCell As Range
Dim sValue As Variant
Dim dField As Variant
For Each sCell In srrg.Cells
sValue = sCell.Value
If Not IsError(sValue) Then
If Len(CStr(sValue)) > 0 Then
dField = Application _
.Match(sCell.Offset(, scOffset).Value, dhrg, 0)
If IsNumeric(dField) Then
drg.AutoFilter Field:=dField, Criteria1:=sValue
End If
End If
End If
Next sCell
SafeExit:
With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Exit Sub
ClearError:
Debug.Print "'" & ProcName & "' Run-time error '" _
& Err.Number & "':" & vbLf & " " & Err.Description
Resume SafeExit
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/410268.html
標籤：