|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 袁振涛 于 2017-10-22 22:33 编辑
- Public d As Object
- Sub aa()
- Set d = CreateObject("Scripting.dictionary")
- Dim Arr
- ReDim Brr(1 To 1000)
- Arr = [a1].CurrentRegion
- For i = 3 To UBound(Arr)
- If Arr(i, 4) <> "标准件" And Arr(i, 4) <> "外购件" Then
- If Arr(i, 1) = 1 Then '一级
- AddNode Arr(i, 3), "1"
- Brr(i) = getFirstNodeNumber(Arr(i, 3))
- End If
- If Arr(i, 1) = 2 Then '二级
- AddNode Arr(i, 3), "2"
- Brr(i) = getSecondNodeNumber(Arr(i, 3))
- End If
- If Arr(i, 1) = 3 Then '三级
- AddNode Arr(i, 3), "3"
- Brr(i) = getThirdNodeNumber(Arr(i, 3))
- End If
- If Arr(i, 1) = 4 Then '四级
- AddNode Arr(i, 3), "4"
- Brr(i) = getFourNodeNumber(Arr(i, 3))
- End If
- Else
- Brr(i) = ""
- End If
- Next
- Sheets("测试用").[f1].Resize(UBound(Brr), 1) = (Application.Transpose(Brr))
- End Sub
- Sub AddNode(ByVal strType$, strLevel$)
- d(strType & strLevel) = d(strType & strLevel) + 1
- d(strLevel) = strType
- For Each Key In d
- If Right(Key, 1) > Val(strLevel) Then
- d.Remove Key
- End If
- Next
- End Sub
- Function getFirstNodeNumber(ByVal strContent$) '一级装配体
- If strContent = "sldprt" Then
- getFirstNodeNumber = "YTKQFMS-0000-000-" & Format(d("sldprt1"), "000") & "-0"
- Else
- getFirstNodeNumber = "YTKQFMS-" & Format(d("sldasm1"), "0000") & "-000-000-0"
- End If
- End Function
- Function getSecondNodeNumber(ByVal strContent$) '二级
- If d("1") = "sldasm" And strContent = "sldprt" Then
- getSecondNodeNumber = "YTKQFMS-" & Format(d("sldasm1"), "0000") & "-000-" & Format(d("sldprt2"), "000") & "-0"
- End If
- If d("1") = "sldasm" And strContent = "sldasm" Then
- getSecondNodeNumber = "YTKQFMS-" & Format(d("sldasm1"), "0000") & "-" & Format(100 * d("sldasm2"), "000") & "-000-0"
- End If
- If d("1") = "sldprt" And strContent = "sldprt" Then
- getSecondNodeNumber = "YTKQFMS-" & Format(d("sldprt2"), "0000") & "-000-" & Format(d("sldprt1"), "000") & "-0"
- End If
- If d("1") = "sldprt" And strContent = "sldasm" Then
- getSecondNodeNumber = "YTKQFMS-0000-" & Format(d("sldasm2"), "000") & Format(100 * d("sldprt1"), "000") & "-0"
- End If
- End Function
- Function getThirdNodeNumber(ByVal strContent$) '三级,拆分二级来得到
- Dim strResult
- strResult = Split(getSecondNodeNumber(d("2")), "-")
- If strContent = "sldprt" Then
- getThirdNodeNumber = strResult(0) & "-" & strResult(1) & "-" & strResult(2) & "-" & Format(Val(strResult(3)) + d("sldprt3"), "000") & "-" & strResult(4)
- Else
- getThirdNodeNumber = strResult(0) & "-" & strResult(1) & "-" & Format(Val(strResult(2)) + d("sldasm3"), "000") & "-" & strResult(3) & "-" & strResult(4)
- End If
- End Function
- Function getFourNodeNumber(ByVal strContent$) '四级,拆分三级来得到
- Dim strResult
- strResult = Split(getThirdNodeNumber(d("3")), "-")
- If strContent = "sldprt" Then
- getFourNodeNumber = strResult(0) & "-" & strResult(1) & "-" & strResult(2) & "-" & Format(Val(strResult(3)) + d("sldprt4"), "000") & "-" & strResult(4)
- End If
- End Function
复制代码
|
|