- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 1# HSINLI
¥iקï¦p¤U- For i = 2 To ¥æ.Range("d2").End(xlDown).Row
- If ¥æ.Range("c" & i).RowHeight Then 'RowHeight > 0 'Àx¦s®æ¤£¬OÁôÂêº
- If ¥æ.Range("c" & i) = "Bought" Then
- po.Range("c" & ipo) = po.Range("c" & ipo) + ¥æ.Range("e" & i)
- ElseIf ¥æ.Range("c" & i) = "Sold" Then
- po.Range("c" & ipo) = po.Range("c" & ipo) - ¥æ.Range("e" & i)
- End If
- End If
- Next i
½Æ»s¥N½X ¤]¥i¦p¦¹- Option Explicit
- Sub Ex()
- Dim po As Worksheet, ¥æ As Worksheet
- Dim ipo As Integer, Bought As Integer, Sold As Integer
- Set po = Sheets("position")
- Set ¥æ = Sheets("¥æ©ö¬ö¿ý")
- Application.ScreenUpdating = False
- po.Range("a5", po.[A5].End(xlDown)) = ""
- 'AdvancedFilter xlFilterCopy ¦b«ü©wªº¨ä¥L½d³òªºÄæ¦ì(µL¦r¦ê,½Æ»s¥þ³¡Äæ¦ì¡C¦³¦r¦ê,¶·¬°¸ê®Æ®wªºÄæ¦ì)
- ¥æ.ListObjects("ªí®æ1").Range.Range("D:D").AdvancedFilter xlFilterCopy, , po.Range("a5"), True
-
- 'AdvancedFilter(¶i¶¥¿z¿ï) ,AutoFilter(¦Û°Ê¿z¿ï)
- 'AdvancedFilter Action:= xlFilterInPlace ¦b¿z¿ïªº½d³òÅã¥Ü¿z¿ï«áªº¸ê®Æ
- With po
- For ipo = 6 To .Range("a5").End(xlDown).Row '
- ¥æ.ListObjects("ªí®æ1").Range.AutoFilter field:=4, Criteria1:=.Range("a" & ipo)
- With ¥æ.ListObjects("ªí®æ1").Range
- .AutoFilter field:=3, Criteria1:="Bought"
- Bought = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
- 'Application.Sum ¤u§@ªí¨ç¼Æ
- '.SpecialCells [¯S®íªºÀx¦s®æ] (xlCellTypeVisible) °Ñ¼Æ:=¥i¨£ªºÀx¦s®æ
- .AutoFilter field:=3, Criteria1:="Sold"
- Sold = Application.Sum(.Range("E:E").SpecialCells(xlCellTypeVisible))
- End With
- .Cells(ipo, "C") = Bought - Sold
- Next
- End With
- ¥æ.ListObjects("ªí®æ1").Range.AutoFilter '¨S¦³·Ç«h = ¨ú®ø¦Û°Ê¿z¿ï
- Application.ScreenUpdating = True
- End Sub
½Æ»s¥N½X |
|