|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
Sub test()
Dim xsrr, sjkrr, jgrr()
xsrr = Sheets("系数").[a1].CurrentRegion
sjkrr = Sheets("数据库").[a1].CurrentRegion
r1 = UBound(xsrr): r2 = UBound(sjkrr)
For i = 2 To r1
For j = 2 To r2
If CStr(sjkrr(j, 1)) = CStr(xsrr(i, 2)) Then mjY25 = sjkrr(j, 25) '查找到记录母级,并记录母级的Y列值。不保证顺序的情况下先全部查一遍
Next
For j = 2 To r2
If (CStr(Left(sjkrr(j, 1), Len(xsrr(i, 2)))) = CStr(xsrr(i, 2))) And (CStr(sjkrr(j, 1)) <> CStr(xsrr(i, 2))) And (sjkrr(j, 25) <> 0) Then '转换为字符进行比较,根据左边字符是否一致来判断是否为子级,排除相等的情况
m = m + 1
ReDim Preserve jgrr(1 To 7, 1 To m)
jgrr(1, m) = sjkrr(j, 1)
jgrr(2, m) = sjkrr(j, 2)
jgrr(3, m) = xsrr(i, 1)
jgrr(4, m) = sjkrr(j, 4)
jgrr(6, m) = sjkrr(j, 23)
If xsrr(i, 5) = 0 Then
jgrr(5, m) = xsrr(i, 7) * xsrr(i, 4) * (sjkrr(j, 25) / mjY25) / jgrr(6, m)
jgrr(5, m) = Round(jgrr(5, m), xsrr(i, 6))
Else: jgrr(5, m) = xsrr(i, 5) * xsrr(i, 4): jgrr(5, m) = Round(jgrr(5, m), xsrr(i, 6))
End If
jgrr(7, m) = jgrr(5, m) * jgrr(6, m): jgrr(7, m) = Round(jgrr(7, m), 2)
End If
Next
Next
Sheets("结果").Select
Sheets("结果").[a1].CurrentRegion.Offset(1) = ""
Sheets("结果").[a2].Resize(UBound(jgrr, 2), UBound(jgrr, 1)) = Application.WorksheetFunction.Transpose(jgrr)
End Sub
|
评分
-
1
查看全部评分
-
|