僅供參考
- Sub test()
- Dim p As Byte, n As Byte, i As Long, c As Object, fadd As String, s
- Dim Sh As Worksheet
- Set Sh = Sheets("Sheet2")
- For p = 3 To 9 Step 6 'C、I欄
- n = n + 1
- For i = 2 To Sh.Cells(Sh.Rows.Count, 13).End(xlUp).Row 'M2~M最後非空行循環
- s = 0
- With Sh.Range(Sh.Cells(1, p), Sh.Cells(Sh.Cells(Sh.Rows.Count, p).End(xlUp).Row, p)) '查找C、I欄資料
- Set c = .Find(Sh.Cells(i, 13), LookIn:=xlValues, lookat:=xlWhole) '查找符合M的品名
- If Not c Is Nothing Then
- fadd = c.Address
- Do
- s = s + c.Offset(, 1) '分別收回、發出累加
- Set c = .FindNext(c) '查找下個符合相同的品名
- Loop While Not c Is Nothing And c.Address <> fadd
- End If
- End With
- If s = 0 Then
- Sh.Cells(i, 13 + n) = ""
- Else
- Sh.Cells(i, 13 + n) = s '收回、發出累計
- End If
- If Sh.Cells(i, 14) - Sh.Cells(i, 15) + Sh.Cells(i, 16) = 0 Then
- Sh.Cells(i, 17) = ""
- Else
- Sh.Cells(i, 17) = Sh.Cells(i, 14) - Sh.Cells(i, 15) + Sh.Cells(i, 16) '總數
- End If
- Next i
- Next p
- End Sub
复制代码 |