- ©«¤l
- 967
- ¥DÃD
- 0
- ºëµØ
- 0
- ¿n¤À
- 1001
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN XP
- ³nÅ骩¥»
- OFFICE 2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-11-29
- ³Ì«áµn¿ý
- 2022-5-17
|
¥»©«³Ì«á¥Ñ register313 ©ó 2011-12-17 20:06 ½s¿è
¦^´_ 3# JEAN
ªì¾ÇªÌVBA- Private Sub CommandButton1_Click()
- º¶
- End Sub
- Private Sub worksheet_activate()
- Application.ScreenUpdating = False
- Rows("3:28") = ""
- Range("H2:I2") = ""
- Sheets("¶i³f¦s®w©ú²Óªí").Range("B3:B10000").Copy Sheets("®w¦s").Range("H3")
- Sheets("®w¦s").Range("H2:I2") = "°Ó«~½s¸¹"
- Range("H2:H10000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("I2:I3"), CopyToRange:=Range("A2"), Unique:=True
- I = 3
- Do While Cells(I, 1) <> ""
- X = Application.SumIf(Sheets("¶i³f¦s®w©ú²Óªí").Range("B3:B10000"), Cells(I, 1), Sheets("¶i³f¦s®w©ú²Óªí").Range("E3:E10000"))
- Y = Application.SumIf(Sheets("»â¥Î°O¿ý©ú²Óªí").Range("D3:D10000"), Cells(I, 1), Sheets("»â¥Î°O¿ý©ú²Óªí").Range("G3:G10000"))
- Cells(I, 4) = X - Y
- Cells(I, 2) = Application.VLookup(Cells(I, 1), Sheets("¶i³f¦s®w©ú²Óªí").Range("B3:K10000"), 2, 0)
- Cells(I, 3) = Application.VLookup(Cells(I, 1), Sheets("¶i³f¦s®w©ú²Óªí").Range("B3:K10000"), 3, 0)
- Cells(I, 5) = Application.VLookup(Cells(I, 1), Sheets("¶i³f¦s®w©ú²Óªí").Range("B3:K10000"), 5, 0)
- Cells(I, 6) = Application.VLookup(Cells(I, 1), Sheets("¶i³f¦s®w©ú²Óªí").Range("B3:K10000"), 6, 0)
- I = I + 1
- Loop
- Columns("H:I") = ""
- Range("A1").Select
- Application.ScreenUpdating = True
- End Sub
½Æ»s¥N½X
»s§@¤å¨ã1.zip (66.51 KB)
·½¦ÛHsieh¶Wª©¤j¤§»s§@¤å¨ã.xls |
|