这种编码有什么问题?我收到对象工作表的错误消息方法范围失败

Question

Sub navrec()
Dim uut As Double
Dim defaultrange As Range
Dim colk As Range
Set colk = Range("k:k")
Set defaultrange = Range("a7:s50000")
Sheet4.Range(defaultrange).AutoFilter Field:=4, Criteria1:="11", Operator:=xlOr, Criteria2:="32"
With Sheet4.AutoFilter.Range

uut = Application.WorksheetFunction.Sum(colk.SpecialCells(xlCellTypeVisible))
Sheet5.Range("c31") = uut
End With

End Sub

Answer 1

你有点

搞砸了范围引用

这是具有正确范围处理的代码(注释中的解释(

Sub navrec()
    Dim defaultrange As Range
    Dim colk As Range
    With Sheet4 ‘reference sheet 4 . From now and till next closing ‘End With’ statement all its members (like ranges) will be accessed by just premitting a dot
        Set defaultrange = .Range("a7:s50000" ‘ set referenced sheet range a7:s50000 as ‘defaultrange’ range . From now on every reference to ‘defaultrange’ will bring you to Sheet4 range a7:s50000
        Set colk = .Range("k:k") ‘ set referenced sheet column K as ‘colk’ range. From now on every reference to ‘colk’ will bring you to Sheet4 range k:k
    End With ‘ stop referencing sheet4
    defaultrange.AutoFilter Field:=4, Criteria1:="11", Operator:=xlOr, Criteria2:="32" ‘ filter ‘defaultrange’, i.e. Sheet4 range a7:s50000
    Sheet5.Range("c31") = =Application.WorksheetFunction.Sum(colk.SpecialCells(xlCellTypeVisible)) ‘fill sheet5 C31 cell with the sum of Sheet4 range K:K filterd cells
End Sub