Sub 宏1()
'
arr = Sheet2.UsedRange
For j = 2 To Sheet4.Range("A65535").End(xlUp).Row
Sheet4.Cells(j, 7) = qiuhe(arr, "不卖的", "先付款的", Sheet4.Cells(j, 1))
Sheet4.Cells(j, 8) = qiuhe(arr, "卖的", "先付款的", Sheet4.Cells(j, 1))
Sheet4.Cells(j, 9) = qiuhe(arr, "不卖的", "要付款的", Sheet4.Cells(j, 1))
Sheet4.Cells(j, 10) = qiuhe(arr, "卖的", "要付款的", Sheet4.Cells(j, 1))
Next
End Sub
Function qiuhe(arr, 条件1, 条件2, num)
a = 0
For i = 1 To UBound(arr)
If arr(i, 5) = num And arr(i, 1) = 条件1 And arr(i, 3) = 条件2 Then
If arr(i, 10) = "正" Then
a = a + arr(i, 11)
End If
If arr(i, 10) = "负" Then
a = a - arr(i, 11)
End If
End If
Next
qiuhe = a
End Function
|