Sub zz()
Dim d, ar, br
Set d = CreateObject("Scripting.Dictionary")
ar = [j1].CurrentRegion
br = Range("a1:h" & [e65536].End(3).Row)
For i = 2 To UBound(ar)
s = Split(ar(i, 2), ";")
ReDim aa(1 To UBound(s) + 1, 1 To 2)
For x = 0 To UBound(s)
aa(x + 1, 1) = Split(s(x), ":")(0): aa(x + 1, 2) = Split(s(x), ":")(1)
Next
d(ar(i, 1)) = aa
Next
On Error Resume Next
For i = 2 To UBound(br)
If d.exists(br(i, 1)) Then k = d(br(i, 1))
kk = br(i, 5)
For x = 1 To UBound(k)
If InStr(kk, k(x, 1)) Then
kk = Replace(kk, k(x, 1), k(x, 2))
End If
Next
If InStr(kk, "=") Then
br(i, 6) = Evaluate(Split(kk, "=")(0))
br(i, 7) = Val(Split(kk, "=")(1))
br(i, 8) = br(i, 6) * br(i, 7): kk = ""
End If
Next
[a1].Resize(UBound(br), UBound(br, 2)) = br
End Sub
|