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

[µo°Ý] Calculate¡A¯à§_¦^¶Çµo¥ÍÅܰʪºÀx¦s®æªºÄæ¦ì

[µo°Ý] Calculate¡A¯à§_¦^¶Çµo¥ÍÅܰʪºÀx¦s®æªºÄæ¦ì

¥»©«³Ì«á¥Ñ ketrddem ©ó 2016-5-1 19:10 ½s¿è

Worksheet_Calculate¨Æ¥ó¤¤
°²³]§Ú«ü©wªº½d³ò¬°sheet1.Columns("A:S").Calculate(²Ä¤@±iªí)
¤j¬ù¦³300¦C

¦pªG¬Y­Ó¤½¦¡¹Bºâ²Å¦X±ø¥ó¡A´N·|Åܦ¨¡uYES¡v¡A¨S¦³²Å¦X±ø¥ó¡A´N¬OªÅ¥Õ¡u" "¡v

½Ð°Ý¡A¦³¨S¦³¤°»ò¤èªk¯à°÷Åý§Ú¦b²Ä¤@®É¶¡ª¾¹D¬O¨º¤@­ÓÄæ¦ì²£¥ÍÅÜ°Ê©O

¤ñ¤è¡AÀx¦s®æ¡uD5¡v¡A²Å¦X±ø¥ó¤F¡A±qªÅ¥Õ­ÈÅܦ¨¡uYES¡v

µ{¦¡´N·|¦^¶Ç¨ìsheet2¡A¤ñ¤è¦^¶Ç­È¬°¡uD5µo¥ÍYES¨Æ¥ó¡v(§âµo¥ÍªºÄæ¦ì¦^¶Ç¨ì²Ä¤G±iªí)
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-5-2 07:47 ½s¿è

¦^´_ 1# ketrddem
´£¨Ñ°Ñ¦Ò¡G
E1.png
2016-5-2 05:43

ThisWorkbook:¡G
  1. Sub Ex()
  2.     [A1] = 10
  3.     [B1] = 8
  4.     [A1] = 6
  5. End Sub
½Æ»s¥N½X
¤u§@ªí1¡G
  1. Private Sub Worksheet_Calculate()
  2.     Dim target As Range
  3.     Set target = Range("C1")

  4.     '  If Not Intersect(target, Range("C1")) Is Nothing Then
  5.     If target.Value = "YES" Then
  6.         '  Run my VBA code
  7.         MsgBox "Worksheet_Calculate is Activvated!"
  8.     End If
  9. End Sub

  10. Private Sub Worksheet_Change(ByVal target As Range)
  11.     '  If Not Intersect(target, Range("C1")) Is Nothing Then
  12.     If target.Address = "$C$1" Then
  13.         '  MyMacro()
  14.         MsgBox "Worksheet_Change = " & target.Address
  15.     End If
  16. End Sub
½Æ»s¥N½X
½Ð¥Î F8 ¨Ó Trace ¨Ó¥J²ÓÆ[¹î  Worksheet_Calculate() ¥H¤Î
Worksheet_Change(ByVal target As Range) ¬O¦p¦ó¹B§@¡A
¥Ø«e {A1:B1] §¡¬O¥H Assign value ¤è¦¡¶i¦æ¡A¦ý¦pªG¥¦­Ì³£¬°
¤½¦¡±a¤J­È¥i¯à Worksheet_Change() ¤£·|¦³°Ê§@¡A¦p [C1]
¤º®e­È¬°¡G =IF(A1>B1, "YES", "NO")  ¤@¼Ë¡C
{A1:B1] §A¥i¥H±a¤J DDE ¤½¦¡ ¨ÓÆ[¹î¸ê®Æ¶×¤J®É¤§°ÊºAª¬ªp¡C

TOP

¦^´_ 2# c_c_lai

If target.Address = "C1" Then
¶·¤p­×§ï¬°
If target.Address = "$C$1" Then
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-5-2 07:48 ½s¿è

¦^´_ 3# ML089
¦n¤[¨S³sô¤F¡A ÁÂÁ«üÃÒ¡A²¨©¿¤F¡C
  1. target.Address = "$A$1" : True : Boolean
  2. target.Address = "A1"   : False : Boolean
½Æ»s¥N½X

TOP

¦^´_ 2# c_c_lai

°õ¦æªº¨BÆJ¸ò§Ú·Q¹³ªºÅ޿観ÂI¤£¦P¡A·íA1­È³Q§ïÅܮɡAC1¤½¦¡¤~·|³s°Ê(IJ°Ê)§ïÅÜ¡C
§Úı±o¶¶§ÇÀ³¸Ó A1§ïÅÜ¡AWorksheet_ChangeÀ³¸Ó¥ý³QIJ°Ê¡A¦A¨ÓC1¤½¦¡§ïÅÜ¡A¦Ó«á Worksheet_Change³QIJ°Ê¡C
¤£ª¾¹D¦³¦³¦ó¬Ýªk?



¥Ø«e°õ¦æ¶¶§Ç¦p¤U

VBA

STEP 1
[A1] = 10

STEP2 ¤½¦¡
C1 = IF(A1>B1, "YES", "NO")

STEP 3
Private Sub Worksheet_Calculate()

STEP 4
Private Sub Worksheet_Change(ByVal Target As Range)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089
¤£¦n·N«ä¡A¥X¥h¨ì°êµ|§½³øµ|¡A­è±q¥~ÀY¦^¨Ó¡C

Step 1:
·í [A1] = 10¡A  Excel ¨t²Î·|¥ýIJ°Ê
Worksheet_Calculate() µM«á±µµÛ¦AIJ°Ê
Worksheet_Change(ByVal target As Range)
³o®É target ¬° [A1]¡F
[C1] «h¥Ñ¤½¦¡¦Û°Ê§PÂ_¡A­È¬° "YES"
¨Ã¥¼¦AIJµo¥ô¦ó Trigger¡C

Step 2:
·í [B1] = 8¡AExcel ¨t²Î·|¥ýIJ°Ê
Worksheet_Calculate() µM«á±µµÛ¦AIJ°Ê
Worksheet_Change(ByVal target As Range)
³o®É target ¬° [B1]¡F
[C1] «h¥Ñ¤½¦¡¦Û°Ê§PÂ_¡A­È¬° "YES"
¨Ã¥¼¦AIJµo¥ô¦ó Trigger¡C

Step 3:
·í [A1] = 6¡A  Excel ¨t²Î¥ç·|¥ýIJ°Ê
Worksheet_Calculate() µM«á±µµÛ¦AIJ°Ê
Worksheet_Change(ByVal target As Range)
³o®É target ¬° [A1]¡F
[C1] «h¥Ñ¤½¦¡¦Û°Ê§PÂ_¡A­È¬° "NO"¡A¤]
¨Ã¥¼¦AIJµo¥ô¦ó Trigger¡C

¤½¦¡­ÈªºÅܤơA¹ê»Ú¤W¨Ã¤£·|IJµo¥ô¦óª¾ Trigger
¤º¦b¨Æ¥ó (Event)¡C
°²³] [A1]¡B[B1] ¦p¬° DDE ¶×¤J¤§¤½¦¡¡A¶}½L«á¤§
DDE ¼Æ¾Ú­Èªº¥ô¦óÅܰʳ£¤£·|¥hIJµo Worksheet_Change
¦Ü©ó °w¹ï  [A1]¡B[B1] ¨âÄ檺¼Æ¾ÚÅܰʬO§_·|²o°Ê
Worksheet_Calculate() ¡A¹ê¦³«Ý¹ê°ÈÆ[¹î¡A
¦ý¦p¥t¤@Äæ¦ì³]©w¬° =A1¡B©Î =B1¡A«h¸ÓÄæ¦ì¤§¼Æ¾Ú²§°Ê
·|²o°Ê Worksheet_Calculate() ¡B¥H¤Î Worksheet_Change() ªº¡C

TOP

¤£¦n·N«ä¡A¥i¯à¤p§Ì»¡©ú¤£²M

D5¥u¬O­Ó¤ñ¤è¦Ó¤w
¨ä¹êµo¥ÍÅÜ°Ê ¬O¦bA2:S300ªº¥ô¦ó¤@­ÓÀx¦s®æ³£¥i¯àµo¥Í

©Ò¥H¤W­±c_c_lai¤j¤j´£¨Ñªº½d¨Ò¡A³£¬O¦³«ü©wÄæ¦ìªºÅÜ°Ê

¤p§Ì·Q°µªº¬O¡A¦bA2:S300ªº½d³òùØ
¤£ºÞ¨º¤@­ÓÀx¦s®æµo¥ÍÅÜ°Ê
³£¯àÀò±o¡uµo¥ÍÅܰʪº¨º­ÓÀx¦s®æ¦ì§}¡v
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

TOP

°µ­Ó¤p¤pªº»¡©ú¡A¦]¬°Åv­­¦³­­¡A¤£¯à¶Kªþ¥ó¡G

A¨ìSÄ椤¡A·í¤¤DÄ椽¦¡³]¬°¡u¦¨¥æ»ù>¤­¤é§¡½u¡AAND ¤­¤é§¡¶q>¤Q¤é§¡¶q¡AAND KD>30¡v
²Ä¤@¦C¨ì²Ä300¦C·í¤¤¡A°²³]²Ä150¦C¬°ªÑ¸¹1101¡A·íD150Àx¦s®æ²Å¦X±ø¥ó¡A´NÅã¥ÜYES¡C

°ÝÃD¨Ó¤F¡A§Ú·Qª¾¹D¡A²{¦bD150¥¿µo¥Í¤FÅÜ°Ê

¦pªGµLªkÀòª¾Åܰʪº¬OD150³o­ÓÀx¦s®æ

§Ú´NµLªkª¾¹D¡AªÑ¸¹1101¡A¥Ø«e²Å¦X¤F§Úªº¬DªÑªº±ø¥ó¡A¤]´N¬O¡u¦¨¥æ»ù>¤­¤é§¡½u¡AAND ¤­¤é§¡¶q>¤Q¤é§¡¶q¡AAND KD>30¡vªº±ø¥ó

©ó¬O¥G

¤p§Ì·Q¥ÎCALCULATE¤èªk¡A¥h°µ¾ã±i¤u§@ªí³]­p

µM«á¡Aµo¥ÍÅܰʪºÄæ¦ì¡A¯à±o¨ì¸ÓÄæ¦ìªº¦C¸¹¸òÄæ¦W¡A³o¼Ë¤l¤~¯à¥ÎOFFSET©w¦ì¥X¬O¨º¤@ÀɪѲ¼¡A¥H¤Î¦b¤°»ò±ø¥ó¤U²Å¦X¤F¡C

¦A¦¸·PÁ¦U¦ì¥ý¶i«ü¾É¡A¦A½Ð¤£§[«ü±Ð¡CÁÂÁ¡C
EXCEL VBA·s¤â¡A½Ð«e½ú¦h¦h«ü±Ð

TOP

¦^´_ 7# ketrddem
§A¹ê»Ú¶]¤@¤Uµ{¦¡¡A¬Ý¥¦¨ì©³¹ê»Ú·|IJ°Ê¨º¨Ç¨Æ¥ó¡A«Kª¾¡C
¦pªG¬OIJ°Ê _Change §Y¥i¨³³t±oª¾Ä²°ÊÄæ¦ìªº¦ì§}¡C¦p¶È¶È
¥uIJ°Ê _Calculate ¨º´NÀY¤j¤F¡I

TOP

¦^´_ 8# ketrddem
§A¥i¥H±NÀÉ®×À£ÁY¦¨ .rar À£ÁYÀÉ«K¯à¤W¶Ç¤F¡A
¿ï¥Î°ª¯Å¼Ò¦¡¤ºªºÀɮפW¶Ç (°j¯¾°w)¡C

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD