
如上图所示,要修改如下代码里的哪一句才能生成对方科目呢?谢谢!
Sub XXL凭证生成对方科目()
Dim data, i&, dic, temp, X&
data = ThisWorkbook.Worksheets("XXL").UsedRange.Value
Set dic = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(data)
If InStr(dic(data(i, 1) & data(i, 2)), data(i, 4)) = 0 Then
If data(i, 5) <> "" Then
If dic(data(i, 1) & data(i, 2)) = "" Then
dic(data(i, 1) & data(i, 2)) = data(i, 4) & "|"
Else
temp = Split(dic(data(i, 1) & data(i, 2)), "|")
If temp(0) = "" Then
temp(0) = data(i, 4)
Else
temp(0) = temp(0) & "," & data(i, 4)
End If
dic(data(i, 1) & data(i, 2)) = Join(temp, "|")
End If
Else
If dic(data(i, 1) & data(i, 2)) = "" Then
dic(data(i, 1) & data(i, 2)) = "|" & data(i, 4)
Else
temp = Split(dic(data(i, 1) & data(i, 2)), "|")
If temp(1) = "" Then
temp(1) = data(i, 4)
Else
temp(1) = temp(1) & "," & data(i, 4)
End If
dic(data(i, 1) & data(i, 2)) = Join(temp, "|")
End If
End If
End If
Next i
For i = 2 To UBound(data)
If data(i, 5) <> "" Then X = 1 Else X = 0
data(i, 8) = Split(dic(data(i, 1) & data(i, 2)), "|")(X) '刮号里的数字表示列号,第一个括号里的8则表示为第8列,生成的对方科目放在此列。
Next i
ThisWorkbook.Worksheets("XXL").[A1].Resize(UBound(data), UBound(data, 2)) = data
End Sub