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

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

¥»©«³Ì«á¥Ñ 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 À˵ø¸ê®Æ)®É,
¹³¤W­z¨º¼Ë¤£«ü©w­n§@¥Îªºª«¥óªº±¡§Î,
¦³¾÷²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

TOP

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

TOP

¥»©«³Ì«á¥Ñ 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¶Ü?

TOP

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§Ì¾Þ§@¶¶§Ç¦³»~¶Ü???

TOP

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)

TOP

¦^´_ 15# luhpro

    §Ú¬O¸ô¤H~~~~~
    §Ú¸ÕµÛ­«·s§â¤w¿é¤Jªº§R°£
    ´ú¸Õªºµ²ªG¦p¤U¹Ï.......¤j®a¬Ý¹ÏÅo
test.gif
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¥»©«³Ì«á¥Ñ 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:
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim lRow As Long
  3.   Dim rStuff As Range, rTemp As Range
  4.   Dim bChecked As Boolean
  5.   Dim vPo, vBalance
  6.   
  7.   With Target.Parent '©ó Sheets("¥X³f¼Æ¶q")
  8.     Set vPo = Sheets("¥¼µ²PO")
  9.     Set rTemp = .Cells(Target.Row, 3)
  10.   
  11.     If .Cells(Target.Row, 2) <> "" And rTemp <> "" And Target.Column < 4 Then
  12.       lRow = .Cells(Rows.Count, 5).End(xlUp).Row + 1
  13.       vBalance = rTemp
  14.       Set rStuff = vPo.[A2] ' ©ó Sheets("¥¼µ²PO")
  15.       
  16.       'Do Until .Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0  ' §ä¨ì¸Ó«È¤á
  17.       '  Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
  18.       'Loop
  19.       
  20.       Do ' Loop Until rStuff = ""
  21.         Do Until (.Cells(Target.Row, 2) = rStuff And rStuff.Offset(, 3) <> 0) Or rStuff = ""  ' §ä¨ì¸Ó«È¤á©Î¤w¨S¸ê®Æ
  22.           Set rStuff = rStuff.Offset(1) ' ²¾¨ì¤U¤@µ§¸ê®Æ
  23.         Loop
  24.         
  25.         Do While rStuff.Offset(, 3) <> 0    ' ÁÙ¦³©|¥¼¥X³fªº¸ê®Æ
  26.           If vBalance > rStuff.Offset(, 3) Then ' ©|¥¼¥X³f¼Æ¶q¤j©ó·í¤U¥¼¥æ¼Æ
  27.             Application.EnableEvents = False
  28.               With .Cells(lRow, 5)
  29.                 .NumberFormat = "yyyy/m/d"
  30.                 .Value = .Parent.Cells(Target.Row, 1) ' ¤é´Á
  31.               End With
  32.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
  33.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
  34.               With .Cells(lRow, 8)
  35.                 .NumberFormat = "#,##0_ "
  36.                 .Value = rStuff.Offset(, 3) ' ¥X³f¼Æ
  37.               End With
  38.               vBalance = vBalance - rStuff.Offset(, 3)
  39.               rStuff.Offset(, 3) = 0
  40.               lRow = lRow + 1
  41.             Application.EnableEvents = True
  42.           Else
  43.             Application.EnableEvents = False
  44.               With .Cells(lRow, 5)
  45.                 .NumberFormat = "yyyy/m/d"
  46.                 .Value = .Parent.Cells(Target.Row, 1) ' ¤é´Á
  47.               End With
  48.               .Cells(lRow, 6) = .Cells(Target.Row, 2) ' «È¤á®Æ¸¹
  49.               .Cells(lRow, 7) = rStuff.Offset(, 1) ' «È¤áPO
  50.               With .Cells(lRow, 8)
  51.                 .NumberFormat = "#,##0_ "
  52.                 .Value = vBalance ' ¥X³f¼Æ
  53.               End With
  54.               rStuff.Offset(, 3) = rStuff.Offset(, 3) - vBalance
  55.             Application.EnableEvents = True
  56.             Exit Sub ' ©|¥¼¥X³f¼Æ¶qµ¥©ó0«h¸õ¥X
  57.           End If
  58.         Loop ' While rStuff.Offset(, 3) <> 0
  59.       Loop Until rStuff = "" ' ª½¨ì³Ì«á¤@µ§¸ê®Æ
  60.         If vBalance > 0 Then ' ¿é¤Jªº¥X³f¼Æ¥¼¯à¥þ³¡¤À°t§¹¦¨
  61.   Application.EnableEvents = False ' ¸T¯à·|³QIJµoªº°Ê
  62.           vTemp = "¿é¤Jªº¥X³f¼Æ¥¼¯à¥þ³¡¤À°t§¹¦¨, ¥X³f¼Æ¥Ñ " & rTemp & "½Õ­°¦Ü "
  63.           rTemp = rTemp - vBalance
  64.           MsgBox vTemp & rTemp
  65.   Application.EnableEvents = True ' ­P¯à(«ì´_)·|³QIJµoªº°Ê§@
  66.         End If
  67.     End If
  68.   End With
  69. 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ªº«ü¥¿.

TOP

¦^´_ 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

TOP

·PÁÂluhpro ¥S¦h¦¸­×§ï¡A¤p§Ì²{¨Ï¥Î¤W¼ÈµL°ÝÃD¡A¦b¦¹¦A¦¸¸Û¤ß·PÁ¡A¯uªºÀ°¤F¤p§Ìªº¤j¦£^^

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦pÆp¥Û¡j®É¶¡¹ï¤@­Ó¦³´¼¼zªº¤H¦Ó¨¥¡A´N¦pÆp¥Û¯ë¬Ã¶Q¡F¦ý¹ï·M¤H¨Ó»¡¡A«o¹³¬O¤@§âªd¤g¡A¤@ÂI»ù­È¤]¨S¦³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD