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

[µo°Ý] VBA °ÝÃD¨D±Ð

[µo°Ý] VBA °ÝÃD¨D±Ð

¥Ø¼Ð·j´M20130406¦³§ï.rar (22.75 KB)

½Ð±ÐVBA¹F¤H­Ì

¦bªþ¥[Àɮ׸̡AexcelÀÉ¡A¸Ì­±¬O©Ò¼gµ{¦¡ªº¬Y³¡¥÷
§Ú¦b¡iSheet¥x¿ï¡j¤¤¼W¥[¤F¤@­ÓCommandButton1¡A¦W¬°¡i­pºâ¡j¡A¤]¼g¦nµ{¦¡½X¡A«ö¤U´N·|­pºâ¡iSheet1¥x¿ï¡j¸Ìªºµ{¦¡¡C
µ{¦¡½X¦p¤U¡G
Private Sub CommandButton1_Click()
   ' MsgBox Range("n5").Value
   Dim i As Integer
   For i = 5 To 14
        Call Macro1(i)
   Next
End Sub

¦ý¡A§Ú·Q¦b¤u§@ªí1¤¤¤]¼W¥[¤@­ÓCommandButton1¡A¤]¦W¬°¡i­pºâ¡j
¦p¦ó¼gµ{¦¡½X¡A·í§Ú«ö¤U¤u§@ªí1ªºCommandButton1®É¡A¤]¥i¥HÁÍ°Ê¡iSheet¥x¿ï¡j¸Ìªºµ{¦¡©O¡H

¥t¥~ÁÙ¦³­Ó°ÝÃD

­Y¬OexcelÀɸ̪º¼i¬ù»ù¦]³sµ²¦ÓÅܰʮɡA¯à¤£¯à¼gµ{¦¡½X¡AÅý§Ú¤£¥Î¡i¤â°Ê¡jªº¤è¦¡´N¯à°õ¦æ¡i­pºâ¡jªº¥\¯à©O¡H
¥Ø«e¦³¬Ý¨ì³o­Óµ{¦¡½X¡A¦p¤U¡G
Private Sub Worksheet_Change(ByVal Target As Range)
¦b¨ä¤º½s½XÅX°Êµ{§Ç(­ì«öÁä«ü©wµ{§Ç)§Y¥i¡C
End Sub

½Ð°Ý³o­Ó­n¼g¦b­þ¸Ì¡H¥i¦æ¶Ü¡H

§Ú¬O¦Û­×ªº¡A©Ò¥H·Q°Ý°Ý¦U¦ì¡A³Â·ÐÀ°­Ó¦£¡AÁÂÁ¡C

¦^´_ 1# ahsiek
¹Ï¸Ñ



·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE


«D±`ÁÂÁ§A¡A³o­Ó¤èªk¥i¦æ¡C

¦ý¤S¥X²{¤F·sªº°ÝÃD
·í§Úªº¤u§@ªí¸Ìªº­È¤£¬O³z¹LDDEªº§ó·s¦ÓÅܰʮɡA³o­Ó¤èªk´NµLªk¦Û°Ê­pºâ¤F

§Úªº·N«ä¬O»¡­ì¥»§Ú¦b¤u§@ªí1¬O¥ÎDDE³sµ²²{³f©Î¼i¬ù»ùªº¼Æ­È¡A·í³o¨Ç¼Æ­È§ó·s®É¡A´N³z¹L³o­Óµ{¦¡½X¥i¥H¦Û°Ê­pºâ
«á¨Ó§Ú¥Î¤u§@ªí2¨Ó­pºâ³o¨Ç­È¡AµM«áª½±µ³sµ²¨ì¤u§@ªí1ªº²{³f©Î¼i¬ù»ùªº­È¸Ì¡A«oµo²{¤u§@ªí1ªº¦Û°Ê­pºâ¤£·|°Ê¤F
°£«D§Úª½±µ¥Î¤â°Êªº¤è¦¡«ö¤U­pºâªºbutton¤~·|­pºâ
§Ú·Q½Ð°Ý­þ¸Ì¥X¿ù¤F¡A¤£¯à¥Î³o­Óµ{¦¡½X¨Ó§ï¶Ü¡H

³Â·Ð¤F¡AÁÂÁ¡C

TOP

¦^´_ 3# ahsiek
§Aªº·N«ä¬O»¡­ì¥»¤u§@ªí1¬O¥ÎDDE³sµ²²{³f©Î¼i¬ù»ùªº¼Æ­È¡ADDE³sµ²²{³f©Î¼i¬ù»ùªº¼Æ­È,²{§ï©ñ¦b¤u§@ªí2­pºâ«á,¦b¤u§@ªí1ªºÀx¦s®æ¥Î¤½¦¡¶Ç¦^¤u§@ªí2³o¨Ç­È.
  1. Option Explicit
  2. ' ** ¤u§@ªí¤º¦³¤½¦¡ªºÀx¦s®æ:¤½¦¡ªº¼Æ­È¦³ÅÜ°Ê,¨Ï¥Î¦¹¤u§@ªíªº¹w³]¨Æ¥ó
  3. Private Sub Worksheet_Calculate()
  4.   Dim i As Integer
  5.   Application.EnableEvents = False
  6.   'EnableEvents ÄÝ©Ê  ¦pªG«ü©wª«¥ó¯àIJµo¨Æ¥ó¡A«h¥»Äݩʬ° True¡CŪ/¼g Boolean¡C
  7.    For i = 5 To 14
  8.         Call Macro1(i)  
  9.    Next
  10.     ''' µ{¦¡½X
  11.     ''' µ{¦¡½X
  12.     ''' µ{¦¡½X
  13.    Application.EnableEvents = True
  14. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¤£¦n·N«ä¡A¤Ó¤[¨S¦³¤W½u¬d¬Ý¡A¯uªº«ÜÁÂÁÂGBKEE¤j¤jªº¦^À³
§Ú¦³¸Õ¹L§Apoªº¤è¦¡¡A¦ýÁÙ¬O¨S¦³¤ÏÀ³¡C

²{¦b§Ú­«·s·Q¤F¤è¦¡   ªþ¥ó¦p®Ç¡G ¥Ø¼Ð·j´M20130..rar (22.57 KB)
­Y§Ú¦b¡i¥x¿ï¡j¡A¼g¦n¤F¥H¤Uªºµ{¦¡½X¡G
Private Sub CommandButton1_Click()
   ' MsgBox Range("n5").Value
   Dim i As Integer
   For i = 5 To 14
        Call Macro1(i)
  
   Next

   
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
   ' MsgBox Range("n5").Value
   Dim i As Integer
   For i = 5 To 14
        Call Macro1(i)
  
   Next

End Sub

µM«á¡A§â¦b¡i¥x¿ï¡jªºÀx¦s®æL5©M¡i¤u§@ªí1¡jªºÀx¦s®æB2°µ³sµ²
§Y¡i¥x¿ï¡jÀx¦s®æL5  = ¡i¤u§@ªí1¡jÀx¦s®æB2
·í§Ú§â¡i¤u§@ªí1¡jªºÀx¦s®æB2 ´«­Ó¼Æ¦r®É
¯à¤£¯à´NÅý¡i¥x¿ï¡jªºÀx¦s®æL5¦]¬°¼Æ¦rÅÜ°Ê¡A¦Ó·|¦Û°Ê¹Bºâ¥H¤W©Ò¼gªºµ{¦¡½X©O¡H

³Â·Ð¤j¤jÀ°­Ó¦£¡A·Q·Q¬Ý¡C¬O¤£¬O­n¦h¤°»òµ{¦¡½X¤~¯à°µ¨ì¡HÁÙ¬O»¡¤£¯à³o¼Ë°µ©O¡H
¤£½×¦p¦ó¡A³£«D±`·PÁ¤j¤jªºÀ°¦£¡C:loveliness:

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-5-2 15:17 ½s¿è

¦^´_ 5# ahsiek
¡i¥x¿ï¡jÀx¦s®æL5  =¤u§@ªí1!B2
·í¡i¤u§@ªí1¡jÀx¦s®æB2 ¦³Åܧó ·|¤Þµo ¡i¥x¿ï¡jªº­«ºâ¨Æ¥ó
  1. Private Sub Worksheet_Calculate()   '¡i¥x¿ï¡jªº­«ºâ¨Æ¥óµ{¦¡½X
  2.   Dim i As Integer
  3.   Application.EnableEvents = False  '°±¤î IJµo¨Æ¥ó
  4.   For i = 5 To 14
  5.         Call Macro1(i)
  6.    Next
  7.    Application.EnableEvents = True  '«ì´_ IJµo¨Æ¥ó
  8. End Sub
½Æ»s¥N½X
  1. Sub Macro1(r As Integer) 'r¬°°Ñ¼Æ¥Nªí¦C¸¹.integer¬°¾ã¼Æ«¬ºA
  2.     With Sheets("¥x¿ï")
  3.         .Range("M" & r).GoalSeek Goal:=.Range("N" & r).Value, ChangingCell:=.Range("D" & r)
  4.     End With
  5. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¯uªº«D±`ÁÂÁÂGBKEE¤j¤jªºÀ°¦£
¬O¥i¦æªº¡A²×©ó¯à¸Ñ¨M¡A¯uªº¦n¶}¤ß©O¡I¡I
­ì¨Ó¼Ò²Õ¨º¸Ì¤]­n¼g¡AÃø©ÇÁ`¬O¥d¦b¨º¸Ì¡C
¦A¦¸·PÁÂGBKEE¤j¤jªºÀ°¦£¡C

TOP

¤£¦n·N«ä¡A¦A³Â·ÐGBKEE¤j¤j

­Y§Ú¤£¥u¦³¡i¥x¿ï¡j¡A§ÚÁÙ¦³¡i¥x¿ï2¡j¡A¨ä¤º®e©M¡i¥x¿ï¡j¬O¤@¼Ëªº¡A¸Ì­±ªº¤½¦¡¦³¨Ç³\¤£¤@¼Ë¦Ó¤w¡A¦ý¤j­P¬Û¦P¡C

©Ò¥H§Ú¥i¥Hª½±µ½Æ»s¡i¥x¿ï¡jªºµ{¦¡½X¶K¦b¡i¥x¿ï2¡j¸Ì¶Ü¡H¦p¤U¡G
Private Sub Worksheet_Change(ByVal Target As Range)
   ' MsgBox Range("n5").Value
   Dim i As Integer
   For i = 5 To 14
        Call Macro1(i)
        
   Next
End Sub


Private Sub Worksheet_Calculate()   '­«ºâ¨Æ¥óµ{¦¡½X
  Dim i As Integer
  Application.EnableEvents = False  '°±¤î IJµo¨Æ¥ó
  For i = 5 To 14
        Call Macro1(i)
   Next
   Application.EnableEvents = True  '«ì´_ IJµo¨Æ¥ó
End Sub

¦Ó¦b¼Ò²Õ¸Ìªºmodule2ªºµ{¦¡½X¥i¥H³o¼Ë¼g¶Ü¡H

Sub Macro1(r As Integer) 'r¬°°Ñ¼Æ¥Nªí¦C¸¹.integer¬°¾ã¼Æ«¬ºA
        With Sheets("¥x¿ï")
            .Range("M" & r).GoalSeek Goal:=.Range("N" & r).Value, ChangingCell:=.Range("D" & r)
        End With
With Sheets("¥x¿ï2")
            .Range("M" & r).GoalSeek Goal:=.Range("N" & r).Value, ChangingCell:=.Range("D" & r)
        End With

    End Sub

¬O¤£¬O³o¼Ë¤£¦æ¡A¦]¬°§Ú³o¼Ë¼g®É¡AEXCEL´N·|·í±¼¡A©Î¬O¥d¥dªº¡C
¥i¥H³Â·Ð®îGBKEE¤j¤j¯à¦AÀ°§Ú¬Ý¤@¤U¶Ü¡H
ÁÂÁ§A¡C

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-5-2 15:28 ½s¿è

¦^´_ 8# ahsiek

'¡i¥x¿ï¡j,¡i¥x¿ï 2¡j ªº¨Æ¥óµ{§Ç
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.    Dim i As Integer
  4.    Application.EnableEvents = False  '°±¤î IJµo¨Æ¥ó
  5.    For i = 5 To 14
  6.         Call Macro1(i, Me)  'ME «üª«¥ó¼Ò²Õ¥»¨­        
  7.    Next
  8.    Application.EnableEvents = True  '«ì´_ IJµo¨Æ¥ó
  9. End Sub
  10. Private Sub Worksheet_Calculate()   '­«ºâ¨Æ¥ó
  11.   Dim i As Integer
  12.   Application.EnableEvents = False  '°±¤î IJµo¨Æ¥ó
  13.   For i = 5 To 14
  14.         Call Macro1(i, Me)
  15.    Next
  16.    Application.EnableEvents = True  '«ì´_ IJµo¨Æ¥ó
  17. End Sub
½Æ»s¥N½X
  1. 'µ{§Ç·s¼W±µ¦¬°Ñ¼Æ «¬ºA¬°Worksheetª«¥ó
  2. Sub Macro1(r As Integer, Sh As Worksheet) 'r¬°°Ñ¼Æ¥Nªí¦C¸¹.integer¬°¾ã¼Æ«¬ºA
  3.     '' Macro1 Macro''
  4.     With Sh
  5.          .Range("M" & r).GoalSeek Goal:=.Range("N" & r).Value, ChangingCell:=.Range("D" & r)
  6.     End With
  7. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦A¦¸ÁÂÁÂGBKEE¤j¤j
§Ú¸Õ¹L¡A¬O¥i¦æªº¡C

¦ý¡A¦]¬°§Ú¥Î¦b¥D­nªºÀɸ̡A¹³¡i¥x¿ï¡jªº¤u§@ªí¦³¤»­Ó¤§¦h
·í§Ú¥u¥ý§ó·s¤G­Ó¤u§@ªíªºµ{¦¡«á¡A¨S·Q¨ìexcel´N·í±¼¤F¡C
­«·s¥´¶}¤]¶]¤£°Ê¤F
¥i¯à¬O§Ú»Ý­n¥Îªº°j°é¤Ó¦h¤F¡A¥H­P©óexcel¶]¤£°Ê¦Ó·í±¼¡C
§Ú¦A·Q·Q¦³¤°»ò¤èªk¦n¤F
¦A¦¸ÁÂÁÂGBKEE¤j¤j

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD