本帖最后由 EXCEL6687 于 2013-10-22 13:37 编辑
以工作表1中A1:A100的数据为条件,在工作表2中A1:A200查找,并将查找结果对应的B列数据写入工作表1B1:B100中,找不到时为空。
- Sub ep()
- Dim d1 As Object, d2 As Object
- Dim i As Integer
- Dim ar(), re()
- Dim sht As Worksheet
- Set d = CreateObject("scripting.dictionary")
- For Each sht In Worksheets
- If sht.Name <> "销量汇总" Then
- If sht.Name = "门市1" Then
- m = 1
- Else
- m = 2
- End If
- ar = sht.Range("A1").CurrentRegion.Value
- For i = 2 To UBound(ar)
- If d.exists(ar(i, 1)) Then
- re(m, d(ar(i, 1))) = re(m, d(ar(i, 1))) + ar(i, 2)
- Else
- k = k + 1
- d(ar(i, 1)) = k
- ReDim Preserve re(1 To 2, 1 To k)
- re(m, k) = ar(i, 2)
- End If
- Next i
- End If
- Next
- Sheets("销量汇总").[A2].Resize(d.Count) = Application.Transpose(d.keys)
- Sheets("销量汇总").[b2].Resize(d.Count, 2) = Application.Transpose(re)
- End Sub
复制代码
销量汇总.rar
(11.7 KB, 下载次数: 6)
|