[已解决]对象不支持

h200797080

我想用sumif来计算 为什么提示对象不支持呢

最佳答案

月排行榜 / 总排行榜

那么的帅

2013-5-7 10:13

h200797080 发表于 2013-5-7 09:05
Sub 库龄分析()
'
' 库龄分析 宏

把 Worksheet  替换成 WorksheetFunction

黄色部分 为

1. For Z = 2 To Range("a65535").End(xlUp).Row
   
2.     Range("T" & Z) = Application.WorksheetFunction.SumIf(Range("A2:a65536"), Range("a" & Z), Range("i2:i65536"))
   
3.     Range("U" & Z) = Application.WorksheetFunction.CountIf(Range("A2:a65536"), Range("a" & Z))
   
4. Next Z
   
5. Range(w3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V3"), Range("i2:i65536"))
   
6. Range(w4) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V4"), Range("i2:i65536"))
   
7. Range(w5) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V5"), Range("i2:i65536"))
   
8. Range(w6) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V6"), Range("i2:i65536"))
   
9. Range(w7) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V7"), Range("i2:i65536"))
   
10. Range(w8) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V8"), Range("i2:i65536"))
    
11. Range(Y3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X3"), Range("i2:i65536"))
    
12. Range(Y4) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X4"), Range("i2:i65536"))
    
13. Range(Y5) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X5"), Range("i2:i65536"))
    
14. Range(Y6) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X6"), Range("i2:i65536"))
    
15. Range(Y7) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X7"), Range("i2:i65536"))
    
16. Range(Y8) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X8"), Range("i2:i65536"))
    
17. Range(AA3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z3"), Range("i2:i65536"))
    
18. Range(AA4) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z4"), Range("i2:i65536"))
    
19. Range(AA5) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z5"), Range("i2:i65536"))
    
20. Range(AA6) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z6"), Range("i2:i65536"))
    
21. Range(AA7) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z7"), Range("i2:i65536"))
    
22. Range(AA8) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z8"), Range("i2:i65536"))

复制代码

跳转到最佳答案楼层

那么的帅

代码语法没问题，可能是源数据的问题

zhoujunah

使用 WorksheetFunction.sumif() 对sumif函数进行调用

h200797080

那么的帅 发表于 2013-5-6 18:26
代码语法没问题，可能是源数据的问题

Sub 库龄分析()
'
' 库龄分析 宏
'
'
ActiveSheet.Select
    Cells.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").Select
    Columns("I:I").ColumnWidth = 17.86
    Columns("I:I").ColumnWidth = 16.57
    Columns("I:I").ColumnWidth = 15.71
    Columns("I:I").ColumnWidth = 14.86
    Columns("H:H").ColumnWidth = 17.43
    Columns("H:H").ColumnWidth = 16.86
    Columns("J:J").ColumnWidth = 5
    Columns("K:K").ColumnWidth = 7.86
    Columns("L:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").ColumnWidth = 8
    Columns("G:G").ColumnWidth = 7.71
    Columns("D:D").ColumnWidth = 7.29
    Range("A2").Select
    Range("O2").Select
    Columns("M:M").Select
    Selection.Delete Shift:=xlToLeft
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "生产日期"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "分类原则"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "当前日期"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "库存天数"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "库存月数"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "库存状态"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "正常长滞"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "合计"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "重复"
    Range("V1").Select
    Range("V3").Select
    ActiveCell.FormulaR1C1 = "成组缸套"
    Range("V4").Select
    Range("V3").Select
    ActiveCell.FormulaR1C1 = "单片环"
    Range("V4").Select
    ActiveCell.FormulaR1C1 = "成组环"
    Range("W4").Select
    Range("V5").Select
    ActiveCell.FormulaR1C1 = "导管"
    Range("V6").Select
    ActiveCell.FormulaR1C1 = "缸套"
    Range("V7").Select
    ActiveCell.FormulaR1C1 = "成组缸套"
    Range("V8").Select
    ActiveCell.FormulaR1C1 = "活塞"
    Range("V9").Select
    ActiveCell.FormulaR1C1 = "四组件"
    Range("V10").Select
    ActiveCell.FormulaR1C1 = "五组件"
    Range("W11").Select
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "正常"
    Range("X3").Select
    ActiveCell.FormulaR1C1 = "单片环正常"
    Range("X4").Select
    ActiveCell.FormulaR1C1 = "成组环正常"
    Range("X5").Select
    ActiveCell.FormulaR1C1 = "导管正常"
    Range("X6").Select
    ActiveCell.FormulaR1C1 = "缸套正常"
    Range("X7").Select
    ActiveCell.FormulaR1C1 = "成组缸套正常"
    Range("X8").Select
    ActiveCell.FormulaR1C1 = "活塞正常"
    Range("X9").Select
    ActiveCell.FormulaR1C1 = "四组件正常"
    Range("X10").Select
    ActiveCell.FormulaR1C1 = "五组件正常"
    Range("Z2").Select
    ActiveCell.FormulaR1C1 = "长滞"
    Range("AA2").Select
    Range("Z3").Select
    ActiveCell.FormulaR1C1 = "单片环长滞"
    Range("Z4").Select
    ActiveCell.FormulaR1C1 = "成组环长滞"
    Range("Z5").Select
    ActiveCell.FormulaR1C1 = "导管长滞"
    Range("Z6").Select
    ActiveCell.FormulaR1C1 = "缸套长滞"
    Range("Z7").Select
    ActiveCell.FormulaR1C1 = "成组缸套长滞"
    Range("Z8").Select
    ActiveCell.FormulaR1C1 = "活塞长滞"
    Range("Z9").Select
    ActiveCell.FormulaR1C1 = "四组件长滞"
    Range("Z10").Select
    ActiveCell.FormulaR1C1 = "五组件长滞"
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    Columns("X:X").EntireColumn.AutoFit
    Columns("Z:Z").EntireColumn.AutoFit
    Range("M2").Select
    Dim x, y As Integer
    For x = 2 To Range("a65535").End(xlUp).Row
    If Left(Range("K" & x), 1) = "K" Then
    Range("m" & x) = "2011" & "-" & Mid(Range("K" & x), 4, 2) & "-" & Mid(Range("K" & x), 2, 2)
    ElseIf Left(Range("K" & x), 1) = "1" Then Range("m" & x) = "20" & Left(Range("K" & x), 2) & "-" & Mid(Range("K" & x), 3, 2) & "-" & Mid(Range("K" & x), 5, 2)
    Else: Range("m" & x) = Left(Range("K" & x), 4) & "-" & Mid(Range("K" & x), 5, 2) & "-" & Mid(Range("K" & x), 7, 2)
    End If
    Range("O" & x) = Date
    Next x
    For y = 2 To Range("a65535").End(xlUp).Row
    If Left(Range("A" & y), 1) = "P" And Range("E" & y) = "活塞环" Then
    Range("N" & y) = "单片环"
    ElseIf Left(Range("A" & y), 1) = "S" And Range("e" & y) = "活塞环" Then Range("N" & y) = "成组环"
    ElseIf Range("E" & y) = "导管" Then Range("N" & y) = "导管"
    ElseIf Left(Range("A" & y), 1) = "P" And Range("E" & y) = "三组件" Then Range("N" & y) = "活塞"
    ElseIf Left(Range("A" & y), 1) = "P" And Range("E" & y) = "三组件" Then Range("N" & y) = "活塞"
    ElseIf Range("E" & y) = "四组件" Then Range("N" & y) = "四组件"
    ElseIf Range("E" & y) = "五组件" Then Range("N" & y) = "五组件"
    ElseIf Left(Range("A" & y), 1) = "P" And Range("e" & y) = "缸套" Then Range("N" & y) = "缸套"
    ElseIf Left(Range("A" & y), 1) = "S" And Range("e" & y) = "缸套" Then Range("N" & y) = "成组缸套"
    End If
    On Error Resume Next
    Range("P" & y) = Range("O" & y) - Range("M" & y)
    Range("Q" & y) = Range("P" & y) / 30
    If Range("Q" & y) > 12 Then
    Range("R" & y) = "长滞"
    Else: Range("R" & y) = "正常"
    End If
    Range("S" & y) = Range("N" & y) & Range("R" & y)
    Next y
    Dim z As Integer
    For z = 2 To Range("a65535").End(xlUp).Row
    Range("T" & z) = Application.Worksheet.SumIf(Range("A2:a65536"), Range("a" & z), Range("i2:i65536"))
    Range("U" & z) = Application.Worksheet.CountIf(Range("A2:a65536"), Range("a" & z))
    Next z
    Range(w3) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("V3"), Range("i2:i65536"))
    Range(w4) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("V4"), Range("i2:i65536"))
    Range(w5) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("V5"), Range("i2:i65536"))
    Range(w6) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("V6"), Range("i2:i65536"))
    Range(w7) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("V7"), Range("i2:i65536"))
    Range(w8) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("V8"), Range("i2:i65536"))
    Range(Y3) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("X3"), Range("i2:i65536"))
    Range(Y4) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("X4"), Range("i2:i65536"))
    Range(Y5) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("X5"), Range("i2:i65536"))
    Range(Y6) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("X6"), Range("i2:i65536"))
    Range(Y7) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("X7"), Range("i2:i65536"))
    Range(Y8) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("X8"), Range("i2:i65536"))
    Range(AA3) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("Z3"), Range("i2:i65536"))
    Range(AA4) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("Z4"), Range("i2:i65536"))
    Range(AA5) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("Z5"), Range("i2:i65536"))
    Range(AA6) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("Z6"), Range("i2:i65536"))
    Range(AA7) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("Z7"), Range("i2:i65536"))
    Range(AA8) = Application.Worksheet.SumIf(Range("n1:n65536"), Range("Z8"), Range("i2:i65536"))


这是代码    黄色是出了问题的  红色是按照昨天的提示修改的   求大神帮助

那么的帅

h200797080 发表于 2013-5-7 09:05
Sub 库龄分析()
'
' 库龄分析 宏

把 Worksheet  替换成 WorksheetFunction

黄色部分 为

1. For Z = 2 To Range("a65535").End(xlUp).Row
   
2.     Range("T" & Z) = Application.WorksheetFunction.SumIf(Range("A2:a65536"), Range("a" & Z), Range("i2:i65536"))
   
3.     Range("U" & Z) = Application.WorksheetFunction.CountIf(Range("A2:a65536"), Range("a" & Z))
   
4. Next Z
   
5. Range(w3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V3"), Range("i2:i65536"))
   
6. Range(w4) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V4"), Range("i2:i65536"))
   
7. Range(w5) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V5"), Range("i2:i65536"))
   
8. Range(w6) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V6"), Range("i2:i65536"))
   
9. Range(w7) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V7"), Range("i2:i65536"))
   
10. Range(w8) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V8"), Range("i2:i65536"))
    
11. Range(Y3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X3"), Range("i2:i65536"))
    
12. Range(Y4) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X4"), Range("i2:i65536"))
    
13. Range(Y5) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X5"), Range("i2:i65536"))
    
14. Range(Y6) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X6"), Range("i2:i65536"))
    
15. Range(Y7) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X7"), Range("i2:i65536"))
    
16. Range(Y8) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("X8"), Range("i2:i65536"))
    
17. Range(AA3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z3"), Range("i2:i65536"))
    
18. Range(AA4) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z4"), Range("i2:i65536"))
    
19. Range(AA5) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z5"), Range("i2:i65536"))
    
20. Range(AA6) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z6"), Range("i2:i65536"))
    
21. Range(AA7) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z7"), Range("i2:i65536"))
    
22. Range(AA8) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("Z8"), Range("i2:i65536"))

复制代码

h200797080

那么的帅 发表于 2013-5-7 10:13
把 Worksheet  替换成 WorksheetFunction

黄色部分 为

我晕 我以为我一直用的就是WorksheetFunction   我换个之后
Range(w3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V3"), Range("i2:i65536"))
    Range(w4) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V4"), Range("i2:i65536"))
    Range(w5) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V5"), Range("i2:i65536"))
    Range(w6) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V6"), Range("i2:i65536"))
    Range(w7) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V7"), Range("i2:i65536"))
    Range(w8) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V8"), Range("i2:i65536"))
    Range(Y3) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("X3"), Range("i2:i65536"))
    Range(Y4) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("X4"), Range("i2:i65536"))
    Range(Y5) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("X5"), Range("i2:i65536"))
    Range(Y6) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("X6"), Range("i2:i65536"))
    Range(Y7) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("X7"), Range("i2:i65536"))
    Range(Y8) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("X8"), Range("i2:i65536"))
    Range(AA3) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("Z3"), Range("i2:i65536"))
    Range(AA4) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("Z4"), Range("i2:i65536"))
    Range(AA5) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("Z5"), Range("i2:i65536"))
    Range(AA6) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("Z6"), Range("i2:i65536"))
    Range(AA7) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("Z7"), Range("i2:i65536"))
    Range(AA8) = Application.WorksheetFunction.SumIf(Range("S1:S65536"), Range("Z8"), Range("i2:i65536"))
这个部分还是没有结果  这个为什么呢

那么的帅

h200797080 发表于 2013-5-7 10:51
我晕 我以为我一直用的就是WorksheetFunction   我换个之后
Range(w3) = Application.WorksheetFunctio ...

Range(w3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V3"), Range("i2:i65536"))

w3 没有加引号，表示变量，就不会显示在w3单元格了

h200797080

那么的帅 发表于 2013-5-7 10:55
Range(w3) = Application.WorksheetFunction.SumIf(Range("n1:n65536"), Range("V3"), Range("i2:i65536" ...

谢谢 为什么有时候明明看到了代码没写 心里老以为写了呢