|
楼主 |
发表于 2015-11-21 08:19
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
已解决:如何用VBA将树形BOM子件对应最近上层的父件找出来
Public Arr, d, k, t
Sub lqxs()
Dim i&, myr&, n&
Set d = CreateObject("scripting.Dictionary")
Application.ScreenUpdating = False
Range("c2:c500").ClearContents
myr = Cells(Rows.Count, 2).End(xlUp).Row
MsgBox myr
Arr = Range("a1:b" & myr)
For i = 2 To UBound(Arr)
n = Len(Arr(i, 1))
d(n) = d(n) & i & ","
Next
k = d.keys: t = d.items
MsgBox d.Count
For i = 1 To UBound(k)
Call yy(t(i), i)
Next
MsgBox "ok"
Application.ScreenUpdating = True
End Sub
Sub yy(tt, c)
Dim t1, t2, j&, aa, sj, i&, bb
t1 = VBA.Left(t(c - 1), Len(t(c - 1)) - 1) '上一级所在的行
t2 = VBA.Left(tt, Len(tt) - 1) '本级所在的行
If InStr(t2, ",") Then
bb = Split(t2, ",")
For j = 0 To UBound(bb)
If InStr(t1, ",") Then
aa = Split(t1, ",")
For i = UBound(aa) To 0 Step -1
If Val(bb(j)) > Val(aa(i)) Then
sj = aa(i) 'sj上一级
Cells(bb(j), 3) = Cells(sj, 2).Value
Exit For
End If
Next
Else
If Val(bb(j)) > Val(t1) Then
sj = t1 'sj-上级;
Cells(bb(j), 3) = Cells(sj, 2).Value
Exit For
End If
End If
Next
Else
sj = t2 - 1: Cells(t2, 3) = Cells(sj, 2).Value
End If
End Sub
已处理,多谢各位高手帮忙!代码如下
|
|