作者：乔山办公网日期：
返回目录：excel表格制作
Sub usa()
Dim BoR, WokeR
Dim WorkLike, Area, Weight, AllArea, AllWeight, AllBo
Dim LikeID(), LikeIDst(), LikeIDwi()
ReDim LikeID(9)
LikeIDst() = Array(1, 3, 5, 9, 13, 17, 19, 20, 21, 24)
LikeIDwi() = Array(2, 2, 2, 4, 4, 2, 1, 1, 3, 3)
For j = 1 To 2
AllBo = 0: AllArea = 0: AllWeight = 0
BoR = Sheets("编码").Cells(65536, j).End(3).Row
WorkLike = Left(Sheets("编码").Cells(1, j), 2)
If Sheets(WorkLike & "生产计划单").[B65536].End(3).Row > 5 Then
Sheets(WorkLike & "生产计划单").Rows("6:" & Sheets(WorkLike & "生产计划单").[B65536].End(3).Row).Delete
End If
Sheets(WorkLike & "生产计划单").Range("A6:E6") = ""
For k = 2 To BoR
WokeR = Sheets(WorkLike & "生产计划单").[B65536].End(3).Row + 1
With Sheets("编码")
For i = 0 To UBound(LikeID())
LikeID(i) = Mid(.Cells(k, j), LikeIDst(i), LikeIDwi(i))
Next
End With
With Sheets(WorkLike & "生产计划单")
.[C4] = Format(Now(), "日 期：yyyy年mm月dd日 hh时mm分 aaaa")
.Rows(WokeR).Insert Shift:=xlDown
.Cells(WokeR, 2) = Application.WorksheetFunction.VLookup(LikeID(1), Sheets("颜色").Range("A:B"), 2, 0) & " " & _
LikeID(2) * 1 & "-" & LikeID(3) * 1 & "*" & LikeID(4) * 1 & "/" & LikeID(8) * 1 & " " & _
Application.WorksheetFunction.VLookup(LikeID(5), Sheets("等级").Range("A:B"), 2, 0) & " " & _
Application.WorksheetFunction.VLookup(LikeID(6), Sheets("包装").Range("A:B"), 2, 0) & _
"/" & Application.WorksheetFunction.VLookup(LikeID(7), Sheets("隔离层").Range("A:B"), 2, 0)
.Cells(WokeR, 3) = LikeID(9) * 1
AllBo = AllBo + LikeID(9) * 1
Area = LikeID(3) / 1000 * LikeID(4) / 1000
AllArea = AllArea + Area * LikeID(8) * LikeID(9)
Weight = LikeID(2) / 1000 * LikeID(3) / 1000 * LikeID(4) / 1000 * 2.5
AllWeight = AllWeight + Weight * LikeID(8) * LikeID(9)
.Cells(WokeR + 1, 1) = "合计："
.Cells(WokeR + 1, 3) = AllBo
.Cells(WokeR + 1, 4) = "合计：" & AllArea & "㎡ 净重" & Format(AllWeight, "0.00") & "吨"
End With
Next
Next
End Sub
等了一天一楼没有回答。一楼搞好了没？手痒。我先交了。
还有，我e69da5e887aae79fa5e98193364改了表格格式，看可以不？
再说了，你的厚度的哪个是不是圈错了，还是打错了。600MM的厚度。有生产这么厚的玻璃吗？
对了，考虑一个工厂发展与客户的要求，不只会生产这几个等级和规格等，我把他们分开表了，只要增加上内容就可以自动填写。
还有，能加分不。写得挺辛苦的。