|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Private Sub CommandButton1_Click()
Dim Cn As Object, Rs As Object, Sq$, ar, i&, s$
Set Cn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
Sheets("报废登记").Activate
ar = [a1].CurrentRegion.Resize(, 4)
ReDim br(2 To UBound(ar), 0)
For i = 2 To UBound(ar)
s = " WHERE " & ar(1, 1) & "='" & ar(i, 1) & "' AND " & _
ar(1, 2) & "='" & ar(i, 2) & "' AND " & _
ar(1, 3) & "='" & ar(i, 3) & "' AND " & _
ar(1, 4) & "*1<=" & Val(ar(i, 4))
Sq = " SELECT SUM(单件工时) FROM [零件清单$A1:g" & Sheets("零件清单").Cells(Rows.Count, 1).End(xlUp).Row & "]" & s
Rs.Open Sq, Cn, 1, 3
br(i, 0) = Rs(0)
If Rs.State = 1 Then Rs.Close
Next
[g2].Resize(i - 2) = br
Cn.Close
Set Cn = Nothing
Set Rs = Nothing
End Sub |
评分
-
3
查看全部评分
-
|