Hsieh

¦^´_ 1# ko310kmo
´N¥Î§AªºÅÞ¿è

1. Dim ar
   
2. Private Sub Worksheet_Calculate()
   
3. If IsEmpty(ar) Then ar = [B2:D13]: Exit Sub
   
4. Set rng = [B2:D13]
   
5. For I = 1 To UBound(ar, 1)
   
6.     If ar(I, 2) & ar(I, 3) <> rng(I, 2) & rng(I, 3) Then
   
7.        With Sheets(CStr(ar(I, 1)))
   
8.           Set a = .[H65536].End(xlUp).Offset(1)
   
9.           a = rng(I, 1): a.Offset(, 1) = rng(I, 2): a.Offset(, 3) = rng(I, 3)
   
10.        End With
    
11.     End If
    
12. Next
    
13. ar = rng
    
14. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 4# ko310kmo
Dim ar  '«Å§i¤@­ÓÀRºA°}¦CÅܼÆ
Private Sub Worksheet_Calculate()
If IsEmpty(ar) Then ar = [B2:D13]: Exit Sub
¦pªG¬OªÅ­Èar = [B2:D13]¡A§_«h´Nµ²§ôsubªº·N«ä¶Ü?  '¦pªG³o­ÓÀRºA°}¦CÁÙ¨S«Ø¥ß¡Aªí¥Ü¶}±ÒÀɮ׫á²Ä¤@¦¸­«ºâ®É¡A©Ò¥H¤£¥²°µ¥ô¦ó¤ñ¸û´N°h¥Xµ{§Ç

Set rng = [B2:D13]¡@¡@¡@¡@¬O³]¦¨°}¦Cªº·N«ä¶Ü? rng¬Orangeªº·N«ä¶Ü? ÁÙ¬O¬O¥N¸¹¦Ó¤w?]  '³o­ÓrngÅܼƬO­ÓRange«¬ºAªºÅܼơA´N¬O«ü¦V§ADDE¤½¦¡©Ò¦bªº½d³ò

For I = 1 To UBound(ar, 1)   '·íÀRºA°}¦C¦³³Q¼g¤J¤¸¯Àªº­È¤~·|¶i¨ì¦¹´`Àô
    If ar(I, 2) & ar(I, 3) <> rng(I, 2) & rng(I, 3) Then  '¤ñ¸û°}¦C¸ò½d³òªº­È¬O§_¤@¼Ë
   ¬°¤°»ò·|ª¾¹Dar¡Brng¬OÅÜ°Ê«e«áªº°}¦C?
¦]¬°ar¬Oµ{§Ç³Ì«á§ârngªº­È¤Ï¹L¨Ó³]µ¹ar¡A©Ò¥Har´NÅܦ¨«e¤@¦¸rngªº­È
       With Sheets(CStr(ar(I, 1)))
          Set a = .[H65536].End(xlUp).Offset(1)
©ÒÄݤu§@ªíªºhÄæ³Ì«á¤@µ§¦³¸ê®ÆªºÄæ¦ìªº¤U¤@¦C¡C ¬O³o·N«ä¹ï§a!!   ¦ý¥¦ªº¤U¤@¦æ¤£¬OªÅ¥Õ¶Ü?
'¬O¸ê®Æ§Àªº¤U¤@¦CªÅ¥Õ¨S¿ù¡A§A¤£¬O­n§â³o¨Ç¬ö¿ý¼g¨ì¹ïÀ³ªº¤u§@ªí¤º¶Ü?

          a = rng(I, 1): a.Offset(, 1) = rng(I, 2): a.Offset(, 3) = rng(I, 3)
       End With
    End If
Next
ar = rng   '±Nrngªº­È³]µ¹ar ¡A°µ¤U¤@¦¸­«ºâªº¤ñ¹ï°ò¦
End Sub