ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¨Ì¥¼µ²PO¤Î­n¥X³f¼Æ¶q,¦Û°Ê´«ºâ­n¥X³fªºPO¤Î¼Æ¶q

¨Ì¥¼µ²PO¤Î­n¥X³f¼Æ¶q,¦Û°Ê´«ºâ­n¥X³fªºPO¤Î¼Æ¶q

¦¹°ÝÃD­ìµo©ó¤@¯ë°Ï¡A¦ý¦p¥H¥¨¶°¬O§_¤]¥i¦Û°Ê®M¨ú¡A¬G¦A¦¸±N¦P°ÝÃDµo©ó¦¹°Ï¤¤¡A½Ð¦U¦ì¤j¤j«ü±Ð¡A¥ýÁÂÁ¦U¦ì...

¦pªþ¥ó¡A¤p§Ì¦³¥¼µ²PO¤Î¥X³f¼Æ¶q¤G¤u§@ªí

¥¼µ²PO¡G¬O«ü»P«È¤á¥¼µ²ªºÁ`©ú²Ó
¥X³f¼Æ¶q¡G¦C¥X·í¤Ñ¸Ó«È¤á®Æ¸¹¥X³fªºÁ`¼Æ

§Æ±æ¥i¹F¨ìªº¥\¯à¡G

1.©ó¥¼µ²PO¤u§@ªí¤¤ªºDÄæ¡A¥i¨Ì¥X³f¼Æ¶q¤¤ªº©ú²Ó¡A¦Û°Ê´«ºâ¥X²{¦UPO¥¼µ²¼Æ¶q
2.¥X³f¼Æ¶q¤u§@ªí¤¤ªºE~HÄæ¦ì¡A¥i¨ÌA~¢ÑÄæ¦ì¥X³f¼Æ¦Û°Ê´«ºâ¹ïÀ³ªºPO

Book1.rar (3.42 KB)

¥»©«³Ì«á¥Ñ luhpro ©ó 2012-9-22 07:16 ½s¿è
¦¹°ÝÃD­ìµo©ó¤@¯ë°Ï¡A¦ý¦p¥H¥¨¶°¬O§_¤]¥i¦Û°Ê®M¨ú¡A¬G¦A¦¸±N¦P°ÝÃDµo©ó¦¹°Ï¤¤¡A½Ð¦U¦ì¤j¤j«ü±Ð¡A¥ýÁÂÁ¦U¦ì ...
p6703 µoªí©ó 2012-9-21 10:25
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim iRow%
  3.   Dim rStuff As Range
  4.   Dim bChecked As Boolean
  5.   Dim vPo, vBalance
  6.   
  7.   Set vPo = Sheets("¥¼µ²PO")
  8.   
  9.   With Target.Parent '©ó Sheets("¥X³f¼Æ¶q")
  10.     If .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  11.       iRow = IIf(.Cells(2, 5) = "", 2, .Cells(2, 5).End(xlDown).Row + 1)
  12.       vBalance = .Cells(Target.Row, 3)
  13.       Set rStuff = vPo.[A2] ' ©ó Sheets("¥¼µ²PO")
  14.       
  15.       Do While .Cells(Target.Row, 2) <> rStuff ' §ä¨ì¸Ó«È¤á
  16.         Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
  17.       Loop
  18.       
  19.       Do
  20.         Do While rStuff.Offset(, 3) <> 0    ' ÁÙ¦³©|¥¼¥X³fªº¸ê®Æ
  21.           If vBalance > rStuff.Offset(, 3) Then ' ©|¥¼¥X³f¼Æ¶q¤j©ó·í¤U¥¼¥æ¼Æ
  22.             Application.EnableEvents = False
  23.               .Cells(iRow, 5) = .Cells(Target.Row, 1) ' ¤é´Á
  24.               .Cells(iRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
  25.               .Cells(iRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
  26.               .Cells(iRow, 8) = rStuff.Offset(, 3) ' ¥X³f¼Æ
  27.               vBalance = vBalance - rStuff.Offset(, 3)
  28.               rStuff.Offset(, 3) = 0
  29.               iRow = iRow + 1
  30.             Application.EnableEvents = True
  31.           Else
  32.             Application.EnableEvents = False
  33.               .Cells(iRow, 5) = .Cells(Target.Row, 1) ' ¤é´Á
  34.               .Cells(iRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
  35.               .Cells(iRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
  36.               .Cells(iRow, 8) = vBalance  ' ¥X³f¼Æ
  37.               rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
  38.             Application.EnableEvents = True
  39.             Exit Sub ' ©|¥¼¥X³f¼Æ¶qµ¥©ó0«h¸õ¥X
  40.           End If
  41.         Loop
  42.         Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
  43.       Loop Until .Cells(Target.Row, 2) <> rStuff
  44.     End If
  45.   End With
  46. End Sub
½Æ»s¥N½X
¦Û°Ê®Ö®øªí-Ans.zip (12.04 KB)

Sheets("¥X³f¼Æ¶q") ªº¥kÃä¬O³Æ¥÷°Ñ¦Ò¥Îªº¸ê®Æ.

TOP

¶W·PÁÂluhpro¥S¡A­ì¨Ó¥¨¶°¤]¥i¥H³o»ò¥Î¡A¿é¤J²Å¦Xªº¸ê®Æ«á¦Û°Ê¨q¥X¡A¯u¬OÅý¤p§Ì¡¨¤j¶}²´¬É¡¨
¥u¬O¤p§Ì©Ò¾Ç¦³­­¡A¹ê¤£¤Ó²M·¡¥¨¶°¬O«ç»ò¥h§PÂ_¨q¥X²Å¦XPO¤Î¼Æ¶q¡A¦¹³¡¥÷¬O§_¥i½Ðluhpro¥S¦AÁ¿¸Ñ²M·¡¤@ÂI¡A¦A¦¸·PÁ§A^^

TOP

luhpro¥S¡A¤p§Ì¹ê»Ú°õ¦æµo²{¦³¥H¤U°ÝÃD¡A¦A·Ð½Ð¨ó§U¸Ñ´b¡AÁÂÁÂ..

1.·s¼Wªºµ§¼Æ¸ê®Æ¤£·|®»¨ì
¦]PO·|³°Äò¼W¥[¡A¤p§Ì±N·sPO¸ê®ÆKEY IN "¥¼µ²PO"¤u§@ªí¤¤¡AµM«á¦A©ó¥X³f¼Æ¶qKEY ¤W¥X³fªº¼Æ¾Ú¡A«oµLªk¦Û°Ê¶]¥X·sPO

2.¥X³f¼Æ¶q¤@¥¹KEY IN¿ù»~¡A³y¦¨¥¼µ²POªº¼Æ¾Ú¸òµÛ¿ù»~
§Y¨Ï±N¸Ó¿ù»~¸ê®Æ­«·s¿é¤J¡A¦ý·í¤U¥¼¥æ¼Æ´N¤w¦©°£µLªk¦^±À¨ì¥¿½T¼Æ¶q¡A¦]¦¹¸ê®Æ±N²Ö¿n¿é¤J¡A¦p¦]¤@µ§¿é¿ù´N³y¦¨µLªk¦^±À¡A¨º«ÜÃø¹ê»Ú®Ö¹ï¨ì¯u¥¿¥¼µ²PO

TOP

luhpro¥S¡A¤p§Ì¶}©l¿é¤J¸ê®Æ¡A¤~²Ä¤Gµ§´N¶]¥X¥H¤U¿ù»~¡A½T©w¸ê®Æ¨Ã¥¼¥´¿ù¡A¦Ó"¥¼µ²PO"·í¤U¥¼¥æ¼Æ¤]¨S¿ù¡A¤£ª¾¦]¦ó·|¦p¦¹



·¸¦ì.jpg

TOP

¶W·PÁÂluhpro¥S¡A­ì¨Ó¥¨¶°¤]¥i¥H³o»ò¥Î¡A¿é¤J²Å¦Xªº¸ê®Æ«á¦Û°Ê¨q¥X¡A¯u¬OÅý¤p§Ì¡¨¤j¶}²´¬É¡¨
¥u¬O¤p§Ì©Ò¾Ç ...
p6703 µoªí©ó 2012-9-22 22:23

·í§PÂ_¥X³f¸ê®Æ¿é¤J§¹«á,
¤é´Á»P®Æ¸¹³¡¤À¬O±q¿é¤J°Ïª½±µ±a¹L¥h,¦Ó¥ý¥H®Æ¸¹¬° Key ¨Ì§Ç©¹¤U§ä²Å¦Xªº¥¼µ²PO¸ê®Æ(·í¤U¥¼¥æ¼Æ ¤£¬O 0 ªº),
§ä¨ì«á±N«È¤á PO ±a¹L¥h, ¦A±N­pºâµ²ªG¤À§O±a¤J ·í¤U¥¼¥æ¼Æ »P ¥X³f¼Æ, §PÂ_¬O§_»Ý¥X³f¼Æ³£¤w§¹¦¨, ­Y¥¼§¹¦¨«hÄ~Äò¤W­z´`Àô.

luhpro¥S¡A¤p§Ì¹ê»Ú°õ¦æµo²{¦³¥H¤U°ÝÃD¡A¦A·Ð½Ð¨ó§U¸Ñ´b¡AÁÂÁÂ..
1.·s¼Wªºµ§¼Æ¸ê®Æ¤£·|®»¨ì
¦]PO·|³°Äò ...
p6703 µoªí©ó 2012-9-24 18:12

³o½T¹ê¬O¸Óµ{¦¡¨S¦³³B²zªº³¡¥÷,
1. ­Y¶ñ¸ê®Æ®É¨S¦³±N ´Áªì¥¼¥æ¼Æ ¤]¥[¶ñ¨ì ·í¤U¥¼¥æ¼Æ, ¨º»ò ·í¤U¥¼¥æ¼Æ ¬O 0 ¦ÛµM´N¤£·|¦³·s¥X³f¸ê®Æ¤F.
¥i©ó Sheets("¥¼µ²PO")¥[¤W :
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   With Target.Parent '©ó Sheets("¥¼µ²PO")
  3.     If .Cells(Target.Row, 1) <> "" And .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  4.       With .Cells(Target.Row, 3)
  5.         .NumberFormat = "#,##0_ "
  6.         .Offset(, 1).NumberFormat = "#,##0_ "
  7.         .Offset(, 1) = .Value
  8.       End With
  9.     End If
  10.   End With
  11. End Sub
½Æ»s¥N½X
2. ³o­Ó³B²z¤W»Ý­n¸û¦hªº°Ê§@,»Ý­n¦A·Q¤@¤U, ±ßÂI¦APost.
luhpro¥S¡A¤p§Ì¶}©l¿é¤J¸ê®Æ¡A¤~²Ä¤Gµ§´N¶]¥X¥H¤U¿ù»~¡A½T©w¸ê®Æ¨Ã¥¼¥´¿ù¡A¦Ó"¥¼µ²PO"·í¤U¥¼¥æ¼Æ¤]¨S¿ù¡A¤£ ...
p6703 µoªí©ó 2012-9-25 11:13

³o¥i¯à¬O§A¸ê®Æ¦C¼Æ¶W¹L Integer ¥i³B²z½d³ò, §Ú±N iRow §ï¦¨ lRow ¦p¤U:
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim lRow As Long
  3.   Dim rStuff As Range
  4.   Dim bChecked As Boolean
  5.   Dim vPo, vBalance
  6.   
  7.   Set vPo = Sheets("¥¼µ²PO")
  8.   
  9.   With Target.Parent '©ó Sheets("¥X³f¼Æ¶q")
  10.     If .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  11.       lRow = IIf(.Cells(2, 5) = "", 2, .Cells(2, 5).End(xlDown).Row + 1)
  12.       vBalance = .Cells(Target.Row, 3)
  13.       Set rStuff = vPo.[A2] ' ©ó Sheets("¥¼µ²PO")
  14.       
  15.       Do While .Cells(Target.Row, 2) <> rStuff ' §ä¨ì¸Ó«È¤á
  16.         Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
  17.       Loop
  18.       
  19.       Do
  20.         Do While rStuff.Offset(, 3) <> 0    ' ÁÙ¦³©|¥¼¥X³fªº¸ê®Æ
  21.           If vBalance > rStuff.Offset(, 3) Then ' ©|¥¼¥X³f¼Æ¶q¤j©ó·í¤U¥¼¥æ¼Æ
  22.             Application.EnableEvents = False
  23.               .Cells(lRow, 5) = .Cells(Target.Row, 1) ' ¤é´Á
  24.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
  25.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
  26.               .Cells(lRow, 8) = rStuff.Offset(, 3) ' ¥X³f¼Æ
  27.               vBalance = vBalance - rStuff.Offset(, 3)
  28.               rStuff.Offset(, 3) = 0
  29.               lRow = lRow + 1
  30.             Application.EnableEvents = True
  31.           Else
  32.             Application.EnableEvents = False
  33.               .Cells(lRow, 5) = .Cells(Target.Row, 1) ' ¤é´Á
  34.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
  35.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
  36.               .Cells(lRow, 8) = vBalance  ' ¥X³f¼Æ
  37.               rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
  38.             Application.EnableEvents = True
  39.             Exit Sub ' ©|¥¼¥X³f¼Æ¶qµ¥©ó0«h¸õ¥X
  40.           End If
  41.         Loop
  42.         Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
  43.       Loop Until .Cells(Target.Row, 2) <> rStuff
  44.     End If
  45.   End With
  46. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# p6703
¦]¬°µ{¦¡¸¨¸¨ªø, ©Ò¥H´N¤£¦A¶K¤W¸Ô²Óµ{¦¡ªº¤å¦r¤º®eª½±µ´£¨ÑªþÀÉÅo.
¦Û°Ê®Ö®øªí-Ans2.zip (27.79 KB)

¾Þ§@¤è¦¡ :
¹ï¿é¤J°Ï¤¤¹ï¥ô¤@ "¦³¸ê®Æ" ªº¬Y­ÓÀx¦s®æ«ö "·Æ¹«¥kÁä" ·|±Ò°Ê¸ê®Æ²§°Êµøµ¡,
©ó¦¹µøµ¡¤¤¥i°õ¦æ°w¹ï¸ÓÀx¦s®æ·íµ§¸ê®Æ²§°Ê, ¦p : ´¡¤J¤@¦C¡B§R°£¸Ó¦C ©Î¬O°w¹ï¦UÄ椺®e§@²§°Ê½Õ¾ã.

µ{¦¡¹B§@ªº­ì²z¬O¥ýÁÙ­ì«á¦A³Æ¥÷¥X³f¸ê®Æ,
ª½¨ì¼ÐªºÀx¦s®æ¨º¤@µ§¸ê®Æ(§t),
±µµÛ¨Ì»Ý¨D²§°Ê±ý­«¶Kªº­ºµ§¸ê®Æ,
³Ì«á¦A¤À§O±N©Ò³Æ¥÷ªº¥X³f¸ê®Æ³v­Ó¶K¤W¥h,
¦p¦¹´N¯à¹F¨ì»Ý¨DÅo.


¨â Sheets ¥k°¼¬°°Ñ¦Ò¸ê®Æ,
¤é´Á¿é¤J¨S¦³®M¥Î¤ë¾äª«¥ó,
¥B¦¹Ãþ¿ù»~¨Ã¥¼³]¸m¿ù»~³B²zµ{¦¡.

¥t©ó "¿é¤J°Ï" ¤¤µL¸ê®Æ°Ï°ìªº¿é¤J¤è¦¡»P¤@¯ë Excel ¿é¤J¤è¦¡¬Û¦P,
¨C¿é§¹¤@µ§¸ê®Æ´N·|ª½±µ°µ¥X³f°t¤ñ,
¦Ó¥Ø«e©ó¦¹¤è¦¡¤U¨Ã¥¼³]­p¸ê®ÆÀˮ־÷¨î,
¬G¦Ó³o¸Ì¥i¯à·|µo¥Í¿é¿ù¸ê®Æ³y¦¨¿ù»~ªº±¡§Î.

ÁÙ¦³¥Ø«e¸Óµ{¦¡¨ÃµL "«D·s¼W¸ê®Æ" ¦P®É²§°Ê¨âÄæ¦ì¸ê®Æªº¥\¯à.

TOP

¦^´_ 7# luhpro
Private Sub Worksheet_Change ¬O³o "¥¼µ²PO" ¤u§@ªíªºÄ²°Ê¨Æ¥ó
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. '  With Target.Parent '©ó Sheets("¥¼µ²PO")   '¥i¤£¥²¦³¦¹With
  3.    ' If .Cells(Target.Row, 1) <> "" And .Cells(Target.Row, 2) <> "" And .Cells(Target.Row, 3) <> "" Then
  4.     If Cells(Target.Row, 1) <> "" And Cells(Target.Row, 2) <> "" And Cells(Target.Row, 3) <> "" Then
  5.       With Cells(Target.Row, 3)
  6.         .NumberFormat = "#,##0_ "
  7.         .Offset(, 1).NumberFormat = "#,##0_ "
  8.         .Offset(, 1) = .Value
  9.       End With
  10.     End If
  11. ' End With
  12. End Sub
½Æ»s¥N½X

TOP

·PÁÂluhpro ¥S¶O¤ßªº«ü¾É¡A¤p§Ì¥ý¹ê»Ú®M¥Î¬Ý¬Ý¡A¦³°ÝÃD¦A½Ð°Ý..^^¡C

GBKEEª©¥Dªºµ{¦¡½X¬O­×§ïª©ªº¶Ü???

TOP

¦^´_ 9# p6703
¤£¬O­×§ïª©,¬O»¡©ú luhproªºµ{¦¡¥iºë²

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD