|
Sub aaa()
Dim Cn As Object, StrSQL, Rs As Object, Arr As Variant, i%, j%
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
StrSQL = "Select 材料或设备名称,规格、型号,单位,min(单价) as 单价 From [Sheet1$A3:H] Group by 材料或设备名称,规格、型号,单位"
Rs.Open StrSQL, Cn, 1, 3
Arr = Rs.getrows
For i = 0 To UBound(Arr, 2)
For j = 4 To Range("A" & Rows.Count).End(xlUp).Row
If Cells(j, "C") & Cells(j, "D") & Cells(j, "E") = Arr(0, i) & Arr(1, i) & Arr(2, i) Then Cells(j, "G") = Arr(3, i)
Next j
Next i
End Sub
|
|