¨Ì¥¼µ²PO¤În¥X³f¼Æ¶q,¦Û°Ê´«ºân¥X³fªºPO¤Î¼Æ¶q
- ©«¤l
- 835
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 915
- ÂI¦W
- 13
- §@·~¨t²Î
- Win 10,7
- ³nÅ骩¥»
- 2019,2013,2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-5-3
- ³Ì«áµn¿ý
- 2024-10-25
|
¥»©«³Ì«á¥Ñ luhpro ©ó 2012-10-2 21:19 ½s¿è
¦^´_ luhpro
Private Sub Worksheet_Change ¬O³o "¥¼µ²PO" ¤u§@ªíªºÄ²°Ê¨Æ¥ó
GBKEE µoªí©ó 2012-10-2 10:16
¶â...
§Ú³o¼Ë¼g¬O¨Ì¾Ú§Ú¤§«e¦³¹L¤£¦nªº¸gÅç¦Ó½Õ¾ã¹L¨Óªº,
¦¹«e¦³¹J¨ì¨Ï¥ÎªÌ¤ÏÀ³§@·~¤¤·|¶Ã¸õ¿ù»~°T®§.
«á¨Ó¸g¹L´ú¸Õ¤~µo²{Y¤@ Excel ÀÉ®×¥¿¦b¹B§@ VBA µ{¦¡(§Y¨Ã«D¥¿¦bµ¥«Ý¨Ï¥ÎªÌªº¾Þ§@),
¦Ó¨Ï¥ÎªÌ¤S¦]¦³¨ä¥L»Ý¨D¶}±Ò©Î¤Á´«¨ì¥t¤@Ó Excel µ{¦¡(©Î¬O¨Ï¥ÎªÌ·Q¶Xµ{¦¡¹B§@¤¤ªºµ¥«Ý®É¶¡, ¤Á´«¨ì¥t¤@Ó Sheet À˵ø¸ê®Æ)®É,
¹³¤Wz¨º¼Ë¤£«ü©wn§@¥Îªºª«¥óªº±¡§Î,
¦³¾÷²v·|µo¥Í¯Á¤Þ(§Y©Ò§@¥Îªºª«¥ó¨Ã«Dµ{¦¡³]p®É©Ò¹w´Áªº¨ºÓ)¿ù»~ªºª¬ªp.
§Úı±o³o¦ü¥G¬O¦]¬° Windows ¦h¤u¦Ó Excel VBA ¤S¥i¥H§@¥Î¦b¤£¦PªºÀɮשΠSheet ¶¡©Ò¾ÉPªº°ÝÃD,
©Ò¥H«á¨Ó§Ú³£·|¥Î With «ü©w§@¥Îª«¥ó©ó¨ä¤º¦A¥H . ¨Ó¯Á¤Þ,
¥HÁקKÃþ¦ü§xÂZªºµo¥Í.
³o¬O§Úªº²z¸Ñ,¤]³\¤£¤@©w¹ï,
¦ý¬O¨º®É³o¼Ë°µ¹L«á¸Ó°ÝÃD´N¤£¦Aµo¥Í¤F.
PS: ·í®ÉªºÀô¹Ò¬O Windows XP + Office 2000 Professional |
|
|
|
|
|
|
- ©«¤l
- 134
- ¥DÃD
- 27
- ºëµØ
- 0
- ¿n¤À
- 168
- ÂI¦W
- 0
- §@·~¨t²Î
- WINXP
- ³nÅ骩¥»
- EXCEL 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2010-10-6
- ³Ì«áµn¿ý
- 2018-5-13
|
luhpro¥S¡A¤p§Ì¹ê»Ú´ú¸Õ¡A°£¤F·PÁ¶O®Éªº×§ï¡Aµo²{·s¿é¤J"¥¼µ²PO"ªº¸ê®ÆµLªk¦A©ó"¥X³f¼Æ¶q"¤¤¨q¥X
¨Ò¡G¤p§Ì·s¼W¤F35453 500,000¡AµM«á©ó¥X³f¼Æ¤¤±NìPO¼Æ³£®Ö®ø§¹«á¡A´NµLªk¦A²£¥Í·sPO¥X³f©ú²Ó¤F¡A½Ð°Ý¬O§_¤p§Ì¿é¤J¤è¦¡¦³»~¡A¦A·Ð½Ð¨ó§U¬Ý¬Ý¡AÁÂÁÂ
¥t©ó "¿é¤J°Ï" ¤¤µL¸ê®Æ°Ï°ìªº¿é¤J¤è¦¡»P¤@¯ë Excel ¿é¤J¤è¦¡¬Û¦P,
¨C¿é§¹¤@µ§¸ê®Æ´N·|ª½±µ°µ¥X³f°t¤ñ,==>·í¤U¥¼¥æ¼Æ¬O·|¦Û°Ê¨q¥XKEY INªº¼Æ¶q |
|
|
|
|
|
|
- ©«¤l
- 835
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 915
- ÂI¦W
- 13
- §@·~¨t²Î
- Win 10,7
- ³nÅ骩¥»
- 2019,2013,2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-5-3
- ³Ì«áµn¿ý
- 2024-10-25
|
¥»©«³Ì«á¥Ñ luhpro ©ó 2012-10-3 22:57 ½s¿è
luhpro¥S¡A¤p§Ì¹ê»Ú´ú¸Õ¡A°£¤F·PÁ¶O®Éªº×§ï¡Aµo²{·s¿é¤J"¥¼µ²PO"ªº¸ê®ÆµLªk¦A©ó"¥X³f¼Æ¶q"¤¤¨q¥X
¨Ò¡G ©ó¥X³f¼Æ¤¤±NìPO¼Æ³£®Ö®ø§¹«á¡A´NµLªk¦A²£¥Í·sPO¥X³f©ú²Ó¤F
p6703 µoªí©ó 2012-10-3 10:27
³o¬O¦]¬°¦b Sheets("¥¼µ²PO") ¦³²§°Ê®É ¨Ã¨S¦³°µ¾A·íªº³B²z:
1. ±N«È¤á®Æ¸¹¥[¤J UserForm ¸Ì«È¤á®Æ¸¹ªº¤U©Ô¦¡¿ï³æ¤¤,¥H³Æ«áÄò§@·~¤¤¥i¥H¥¿±`¿ï¾Ü¨ì¸Ó®Æ¸¹.
2. ±N¨Ï¥ÎªÌ¿é¤Jªº "´Áªì¥¼¥æ¼Æ" ¥N¤J¦Ü "·í¤U¥¼¥æ¼Æ" ¤º,
¦]¬°Àx¦s®æ¨S¦³¿é¤J¸ê®Æ®É¨äȬ° 0 ,
¬G¦Ó "·í¤U¥¼¥æ¼Æ" ¬O 0 ªº·s¼W¶µ¥Ø,
¦ÛµM´NµLªk¥¿±`°Ñ»P¥X³f§Ç¦C¤F.
קï«áµ{¦¡¦p¤U:
¦Û°Ê®Ö®øªí-Ans3.zip (33.34 KB)
¨C¿é§¹¤@µ§¸ê®Æ´N·|ª½±µ°µ¥X³f°t¤ñ,==>·í¤U¥¼¥æ¼Æ¬O·|¦Û°Ê¨q¥XKEY INªº¼Æ¶q
¤£À´, ¬Oµ{¦¡ªº¤ÏÀ³¦³°ÝÃD¶Ü? |
|
|
|
|
|
|
- ©«¤l
- 134
- ¥DÃD
- 27
- ºëµØ
- 0
- ¿n¤À
- 168
- ÂI¦W
- 0
- §@·~¨t²Î
- WINXP
- ³nÅ骩¥»
- EXCEL 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2010-10-6
- ³Ì«áµn¿ý
- 2018-5-13
|
luhpro¥S¨Ì´£¨Ñªº¦Û°Ê®Ö®øªí-Ans3¤¤¦b¥¼µ²PO²Ä34¦Cª½±µ¼W¥[¤@µ§35451 500,000¡]¦¹ì¥¼µ²¤w¬°0¡^¡AµM«á¦A©ó¥X³f¼Æ¶qA~CÄæ¦ì¿é¤J¤é´Á¤Î¼Æ¶q¡A¦ý¨Ì¸õ¥X "¿é¤Jªº¥X³f¼Æ¥¼¯à¥þ³¡¤À°t§¹¦¨¡A¥X³f¼Æ¥Ñ500000½Õ°¬°0¡^¡A½Ð°Ý¬O¤p§Ì¾Þ§@¶¶§Ç¦³»~¶Ü??? |
|
|
|
|
|
|
- ©«¤l
- 835
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 915
- ÂI¦W
- 13
- §@·~¨t²Î
- Win 10,7
- ³nÅ骩¥»
- 2019,2013,2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-5-3
- ³Ì«áµn¿ý
- 2024-10-25
|
luhpro¥S¨Ì´£¨Ñªº¦Û°Ê®Ö®øªí-Ans3¤¤¦b¥¼µ²PO²Ä34¦Cª½±µ¼W¥[¤@µ§35451 500,000¡]¦¹ì¥¼µ²¤w¬°0¡^¡AµM«á¦A©ó¥X ...
p6703 µoªí©ó 2012-10-4 12:09
×¥¿¤@¦æ¤º®e :
Do Until .Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0 ' §ä¨ì¸Ó«È¤á
¦Û°Ê®Ö®øªí-Ans4.zip (33.55 KB)
|
|
|
|
|
|
|
- ©«¤l
- 129
- ¥DÃD
- 3
- ºëµØ
- 0
- ¿n¤À
- 178
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-12-28
- ³Ì«áµn¿ý
- 2018-11-10
|
¦^´_ 15# luhpro
§Ú¬O¸ô¤H~~~~~
§Ú¸ÕµÛ«·s§â¤w¿é¤Jªº§R°£
´ú¸Õªºµ²ªG¦p¤U¹Ï.......¤j®a¬Ý¹ÏÅo
|
|
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X
|
|
|
|
|
- ©«¤l
- 835
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 915
- ÂI¦W
- 13
- §@·~¨t²Î
- Win 10,7
- ³nÅ骩¥»
- 2019,2013,2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-5-3
- ³Ì«áµn¿ý
- 2024-10-25
|
¥»©«³Ì«á¥Ñ luhpro ©ó 2012-10-8 22:47 ½s¿è
¦^´_ luhpro
§Ú¬O¸ô¤H~~~~~
§Ú¸ÕµÛ«·s§â¤w¿é¤Jªº§R°£
´ú¸Õªºµ²ªG¦p¤U¹Ï.......¤j®a¬Ý¹Ï ...
softsadwind µoªí©ó 2012-10-6 17:07
קïµ{¦¡¦p¤U:- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim lRow As Long
- Dim rStuff As Range, rTemp As Range
- Dim bChecked As Boolean
- Dim vPo, vBalance
-
- With Target.Parent '©ó Sheets("¥X³f¼Æ¶q")
- Set vPo = Sheets("¥¼µ²PO")
- Set rTemp = .Cells(Target.Row, 3)
-
- If .Cells(Target.Row, 2) <> "" And rTemp <> "" And Target.Column < 4 Then
- lRow = .Cells(Rows.Count, 5).End(xlUp).Row + 1
- vBalance = rTemp
- Set rStuff = vPo.[A2] ' ©ó Sheets("¥¼µ²PO")
-
- 'Do Until .Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0 ' §ä¨ì¸Ó«È¤á
- ' Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
- 'Loop
-
- Do ' Loop Until rStuff = ""
- Do Until (.Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0) Or rStuff = "" ' §ä¨ì¸Ó«È¤á©Î¤w¨S¸ê®Æ
- Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
- Loop
-
- Do While rStuff.Offset(, 3) <> 0 ' ÁÙ¦³©|¥¼¥X³fªº¸ê®Æ
- If vBalance > rStuff.Offset(, 3) Then ' ©|¥¼¥X³f¼Æ¶q¤j©ó·í¤U¥¼¥æ¼Æ
- Application.EnableEvents = False
- With .Cells(lRow, 5)
- .NumberFormat = "yyyy/m/d"
- .Value = .Parent.Cells(Target.Row, 1) ' ¤é´Á
- End With
- .Cells(lRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
- .Cells(lRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
- With .Cells(lRow, 8)
- .NumberFormat = "#,##0_ "
- .Value = rStuff.Offset(, 3) ' ¥X³f¼Æ
- End With
- vBalance = vBalance - rStuff.Offset(, 3)
- rStuff.Offset(, 3) = 0
- lRow = lRow + 1
- Application.EnableEvents = True
- Else
- Application.EnableEvents = False
- With .Cells(lRow, 5)
- .NumberFormat = "yyyy/m/d"
- .Value = .Parent.Cells(Target.Row, 1) ' ¤é´Á
- End With
- .Cells(lRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
- .Cells(lRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
- With .Cells(lRow, 8)
- .NumberFormat = "#,##0_ "
- .Value = vBalance ' ¥X³f¼Æ
- End With
- rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
- Application.EnableEvents = True
- Exit Sub ' ©|¥¼¥X³f¼Æ¶qµ¥©ó0«h¸õ¥X
- End If
- Loop ' While rStuff.Offset(, 3) <> 0
- Loop Until rStuff = "" ' ª½¨ì³Ì«á¤@µ§¸ê®Æ
- If vBalance > 0 Then ' ¿é¤Jªº¥X³f¼Æ¥¼¯à¥þ³¡¤À°t§¹¦¨
- Application.EnableEvents = False ' ¸T¯à·|³QIJµoªº°Ê
- vTemp = "¿é¤Jªº¥X³f¼Æ¥¼¯à¥þ³¡¤À°t§¹¦¨, ¥X³f¼Æ¥Ñ " & rTemp & "½Õ°¦Ü "
- rTemp = rTemp - vBalance
- MsgBox vTemp & rTemp
- Application.EnableEvents = True ' P¯à(«ì´_)·|³QIJµoªº°Ê§@
- End If
- End If
- End With
- End Sub
½Æ»s¥N½X
¦Û°Ê®Ö®øªí-Ans5.zip (34.14 KB)
·íªì·|¥[¨º¤@¬q¤]¬O¦Ò¼{¨ìY¨Ï¥ÎªÌ¤£¤p¤ß¿é¤J¹L¦hªº¥X³f¶q,
·|³y¦¨µLªkÁÙì¦^¥¼¿é¤J«eªºª¬ºA,
µ²ªG¦]¦Ò¼{¤£¶g°j°é±ø¥ó¥¼¸òµÛ½Õ¾ã,
¤Ï¦Ól¥Ó¥X¨ä¥Lªº¿ù»~¥X²{,
ÁÂÁ§Aªº«ü¥¿. |
|
|
|
|
|
|
- ©«¤l
- 129
- ¥DÃD
- 3
- ºëµØ
- 0
- ¿n¤À
- 178
- ÂI¦W
- 0
- §@·~¨t²Î
- XP
- ³nÅ骩¥»
- 2010
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-12-28
- ³Ì«áµn¿ý
- 2018-11-10
|
¦^´_ 17# luhpro
§Ú³o´X¤Ñ«Ü§V¤Oªº¬Ý§A¼gªºµ{¦¡
¦ý¬OÁÙ¬OÀY·w·w ¸ò¤£¤W.......
·PÁ§AÄ@·N§ó·s...Åý§Ú¦³¾Ç²ßªº¾÷·| |
|
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X
|
|
|
|
|
- ©«¤l
- 134
- ¥DÃD
- 27
- ºëµØ
- 0
- ¿n¤À
- 168
- ÂI¦W
- 0
- §@·~¨t²Î
- WINXP
- ³nÅ骩¥»
- EXCEL 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¶³ªL
- µù¥U®É¶¡
- 2010-10-6
- ³Ì«áµn¿ý
- 2018-5-13
|
·PÁÂluhpro ¥S¦h¦¸×§ï¡A¤p§Ì²{¨Ï¥Î¤W¼ÈµL°ÝÃD¡A¦b¦¹¦A¦¸¸Û¤ß·PÁ¡A¯uªºÀ°¤F¤p§Ìªº¤j¦£^^ |
|
|
|
|
|
|