|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub test0()
- Dim ar, br(), cr, dict() As Object
- Dim i As Long, j As Long, x As Long, y As Long
-
- ReDim br(2 To Worksheets.Count), dict(1 To Worksheets.Count)
-
- Set dict(1) = CreateObject("Scripting.Dictionary")
- For j = 2 To Worksheets.Count
- Set dict(j) = CreateObject("Scripting.Dictionary")
- cr = Worksheets(j).Range("A1").CurrentRegion.Offset(1).Value
- For i = 2 To UBound(cr) - 1
- dict(j).Add Trim(cr(i, 1)), i
- Next
- For i = 2 To UBound(cr, 2)
- dict(j).Add Trim(cr(1, i)), i
- Next
- br(j) = cr
- dict(1).Add Worksheets(j).Name, j
- Next
-
- With Sheet1
- .Activate
- ar = .Range("M3", .Cells(.Rows.Count, "E").End(xlUp)).Value
- End With
-
- For i = 1 To UBound(ar)
- If dict(1).Exists(Trim(ar(i, 1))) Then
- j = dict(1)(Trim(ar(i, 1)))
- If dict(j).Exists(Trim(ar(i, 9))) Then
- y = dict(j)(Trim(ar(i, 9)))
- If Val(ar(i, 6)) Then
- If dict(j).Exists(Trim(ar(i, 6))) Then
- x = dict(j)(Trim(ar(i, 6)))
- ar(i, 1) = 1 * br(j)(y, x)
- Else
- If Val(ar(i, 6)) > 10 Then
- x = dict(j)("10")
- ar(i, 1) = 1 * br(j)(y, x) + (Val(ar(i, 6)) - 10) * br(j)(y, x + 1)
- End If
- End If
- End If
- End If
- Else
- ar(i, 1) = "没有报价表"
- End If
- Next
-
- Range("U3").Resize(UBound(ar)) = ar '改为 Range("K3")
-
- For j = LBound(dict) To UBound(dict)
- Set dict(j) = Nothing
- Next
- Beep
- End Sub
复制代码 |
评分
-
2
查看全部评分
-
|