枯禅老师,照着您帮忙编写的数组公式,我也学着向下写,但无法运行了,总是在arrt(k, 3) = "",这个地方报错,麻烦您帮忙看看了,谢谢! Sub 数据源数据生成4444() Dim I&, k&, n& Dim arr, arr0, arrt() Dim d As New Dictionary With Sheets("交易大类") arr = .Range("a2:b" & .[a65536].End(3).Row) End With For I = 1 To UBound(arr) d(arr(I, 1)) = arr(I, 2) Next With Sheets("数据源") arr0 = .Range("a5:Y" & .[H65536].End(3).Row) ReDim arrt(1 To UBound(arr0), 1 To 2) For k = 1 To UBound(arrt) arrt(k, 1) = d(arr0(k, 8)) If arr0(k, 4) = 18 Then arrt(k, 2) = "关联销售" ElseIf arr0(k, 4) = 50 Then arrt(k, 2) = "宠物销售" Else arrt(k, 2) = "非关联销售" End If Next .[a5].Resize(UBound(arrt), 2) = arrt End With With Sheets("交易大类") arr = .Range("a2:b" & .[a65536].End(3).Row) End With For I = 1 To UBound(arr) d(arr(I, 1)) = arr(I, 2) Next With Sheets("数据源") arr0 = .Range("a5:Y" & .[H65536].End(3).Row) ReDim arrt(1 To UBound(arr0), 1 To 2) For k = 1 To UBound(arrt) arrt(k, 1) = d(arr0(k, 8)) If arr0(k, 4) = 18 And arr0(k, 1) = "15GP" Or arr0(k, 1) = "16BR" Then arrt(k, 3) = "关联禽料交易" ElseIf arr0(k, 4) = 18 And arr0(k, 1) = "11HG" Then arrt(k, 3) = "关联猪料交易" Else arrt(k, 3) = "" End If Next .[a5].Resize(UBound(arrt), 2) = arrt End With Set d = Nothing End Sub
|