Office中国论坛/Access中国论坛

标题: 求助VBA的汇总方式 [打印本页]

作者: xjtben 时间: 2008-3-29 23:01
标题: 求助VBA的汇总方式
1.对SHEET(1)!A:F范围里同时满足a(n)=X1,B(n)＝X2,C(n)=X3 条件的E(n)的值
2.对SHEET(1)!A:F范围里同时满足a(n)=X1,B(n)＝X2,C(n)=X3 条件的E(n)的值汇总
请问如何实验，那种方法比较快？

小弟用ｆｏｒ　和ｉｆ条件判断，运算非常慢，请各位指教。

或者如何改写这段代码，令其高效？
Sub sum_m(work, order_m As Double, I As Integer, k As Double)
Dim sum_m1 As Double, sum_m2 As Double, sum_m3 As Double, sum_m4 As Double, sum_m5 As Double
Dim cc As Integer, k1 As Double, k2 As Double, k3 As Double

sum_m1 = 0
sum_m2 = 0
sum_m3 = 0
sum_m4 = 0
sum_m5 = 0
   For cc = 1 To 30000
   If Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 16).Value = "" Then
      Exit For
   Else
      If Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 16).Value = work Then

         k1 = Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 4).Value
         k2 = Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 2).Value
         k3 = Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 10).Value


         If k1 = order_m And k2 = 2 And k3 = 214 Then
         sum_m1 = sum_m1 + Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 7).Value
         Else
         If k1 = order_m And k2 = 2 And k3 = 255 Then
            sum_m2 = sum_m2 + Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 7).Value
         Else
         If k1 = order_m And k2 = 2 And (k3 = 258 Or k3 = 9900) Then
               sum_m3 = sum_m3 + Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 7).Value
         Else
         If k1 = order_m And k2 = 1 And k3 = 300 Then
            sum_m4 = sum_m4 + Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 7).Value
         Else
         If k1 = order_m And k2 = 1 And k3 = 258 Then
            sum_m5 = sum_m5 + Workbooks("基础资料.xls").Sheets("料件").Cells(cc, 7).Value

         End If
         End If
         End If
         End If
         End If
      End If
   End If
Next
Workbooks("情况表.xls").Sheets(work).Cells(I, k + 3).Value = sum_m1
Workbooks("情况表.xls").Sheets(work).Cells(I, k + 4).Value = sum_m2
Workbooks("情况表.xls").Sheets(work).Cells(I, k + 5).Value = sum_m3
Workbooks("情况表.xls").Sheets(work).Cells(I, k + 6).Value = sum_m4
Workbooks("情况表.xls").Sheets(work).Cells(I, k + 13).Value = sum_m5

[ 本帖最后由 xjtben 于 2008-3-29 23:51 编辑 ]

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)