|
Sub ceshi()
Dim i%, j%, mh1%, mh2%
Dim sht1 As Worksheet, sht2 As Worksheet
Set sht1 = Sheets("明细表")
Set sht2 = Sheets("标准")
mh1 = sht1.[a65535].End(xlUp).Row
mh2 = sht2.[a65535].End(xlUp).Row
sht1.Range("f4:f" & mh1 - 1).ClearContents
sht2.Select
Range("h1:l" & mh2 + 1).ClearContents
For j = 1 To 5
mh2 = mh2 - IIf(j = 5, 2, 0)
For i = 3 To mh2
If i = 3 Then
Cells(i, 7 + j) = 0
Else
Cells(i, 7 + j) = Left(Cells(i, j), 2)
End If
Next
Next
sht1.Select
Range("g3") = "类": Range("h3") = "位": Range("i3") = "小位"
Range("g4") = "=IF(D4=""检验"",2,1)"
Range("h4") = "=MATCH($D4,标准!$A$2:$F$2,0)"
Range("i4") = "=MATCH(E4*100,OFFSET(标准!$H$2:$H$9,0,H4-1),1)"
Range("f4") = "=INDEX(OFFSET(标准!$D$2:$D$9,0,IF(G4=1,0,2)),I4)"
Range("f4:i" & mh1).FillDown
Range("f4:i" & mh1) = Range("f4:i" & mh1).Value
Range("g3:i" & mh1).ClearContents
sht2.Range("h1:l" & mh2 + 9).ClearContents
Range("h1") = "技术支持:(QQ)4857066,(微信)dujm312"
MsgBox "执行完毕!"
End Sub
|
|