[µo°Ý] ·íBÄ渹½X»P¹w³]³W«h¤£¦P®É¡A¦p¦ó¼u¥Xµøµ¡´£¿ô¡H

RCRG

BÄæ»P¹w³]¤£¦P´£¿ô.rar (12.18 KB)

¤U¸ü¦¸¼Æ: 5

2015-11-28 15:15

¤U¸ü (36.23 KB)

2015-11-28 15:17

RCRG

RCRG µoªí©ó 2015-11-28 15:17

    ¹ï¤F¡I¥Ñ©ó¦³¨Ç¨Ï¥ÎªÌ¤£¨£ªº·|¥Î "B3~B79Äæ¹w³]³W«h" «öÁä ¥h¶ñ¤J¸¹½X¡A¦Ó¬O¥Î¦P¼ËÅÞ¿è¦Û¤v"¤â°Ê"¤è¦¡¤@¤@¥h¶ñ¤JBÄ渹½X¡A©Ò¥H§Æ±æ¼u¥Xµøµ¡´£¿ôªº¤è¦¡¬O¦³¿é¤J¿ù´£¿ô§Y¥i¡A¤U¤@­ÓÄæ¦ì¤S¿é¤J¿ù´N¦A´£¿ô¡A¥H¦¹Ãþ±À¡A¦ý¦pªG¿é¤J¥¿½T´NµL»Ý´£¿ô¡AÁקK¤@ª½¼u¥Xµøµ¡¡A¼vÅT¨Ï¥ÎªÌ§@·~¡C

¥t¥~¤£ª¾¥i§_¦AÀ°§Ú°µ¤@­Ó "¨ú®ø´£¿ô" ªº«öÁä¥H¨Ñ¤v¨­¥t¥~»Ý¨D¡A¥ý·PÁ¦U¦ì¤j¤j¤F¡I

GBKEE

¦^´_ 2# RCRG


¸Õ¸Õ¬Ý

1. '¤u§@ªíA ¼Ò²Õ
   
2. Option Explicit
   
3. Private Sub Worksheet_Change(ByVal Target As Range)
   
4.     Dim E As Range, M As String
   
5.     'Intersect ¤èªk ¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí¨â­Ó©Î¦h­Ó½d³ò­«Å|ªº¯x§Î½d³ò¡C
   
6.     For Each E In Target
   
7.         If Not Application.Intersect(E, [B3:B79]) Is Nothing Then
   
8.             'IF(C3="""","""",3^(1-COUNT(MATCH(C3,AA$3:AA$500,))))"
   
9.             M = "IF(" & E(1, 2).Address & "="""","""",3^(1-COUNT(MATCH(" & E(1, 2).Address & ",AA$3:AA$500,))))"
   
10.             If E <> Application.Evaluate(M) Then MsgBox E.Address(0, 0) & "= " & E & " ¶ñ¤J¸¹½X»P¹w³]¤£¦P"
    
11.         End If
    
12.     Next
    
13. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

Private Sub Worksheet_Change(ByVal Target As Range)
Dim E As Range, M, N
If [B1] = 111 Then Exit Sub¡@'§@°Ê¡eÃö³¬¡f¡@
For Each E In Target
¡@¡@If Not Application.Intersect(E, [B3:B79]) Is Nothing Then
¡@¡@¡@M = Application.Match(E(1, 2), [AA3:AA500], 0)
¡@¡@¡@If IsError(M) Then N = 3 Else N = 1
¡@¡@¡@If E(1, 2) = "" Then N = ""
¡@¡@¡@If E <> N Then
¡@¡@¡@¡@MsgBox "¿é¤J¤º®e¿ù»~¡I±N¦Û°Ê­×¥¿¡ã¡ã¡@"
¡@¡@¡@¡@Application.EnableEvents = False
¡@¡@¡@¡@E = N
¡@¡@¡@¡@Application.EnableEvents = True
¡@¡@¡@End If
¡@¡@End If
Next
End Sub

­ã´£³¡ªL

¡@¡@¡@If E <> N Then
¡@¡@¡@¡@MsgBox "¿é¤J¤º®e¿ù»~¡I±N¦Û°Ê¡eÁÙ­ì¡f¡ã¡ã¡@"
¡@¡@¡@¡@Application.EnableEvents = False
¡@¡@¡@¡@Application.Undo '°õ¦æÁÙ­ì«ü¥O
¡@¡@¡@¡@Application.EnableEvents = True
¡@¡@¡@End If

RCRG

¦^´_ 3# GBKEE

¦^´_ 5# ­ã´£³¡ªL

·PÁÂGBKEE¤j©M­ã¤jªºµª®×¡AGBKEE¤jªº "¨C¤@Äæ¿ù»~»¡©ú" ©M ­ã¤jªº "¦Û°ÊÁÙ­ì"¡AÅý§Ú¨£ÃѤF¦U¦³¤£¦Pªº³]­p¥©§®¡A¥u¬O¤£ª¾¯à§_À°§Ú³]­p "¤@Áä¨ú®ø´£¿ô" ªº«ö¶s¡A¦]¬°¦³®É·|»Ý­n¿é¤J»P¹w³]¤£¦Pªº¸¹½X¡A¦Ó­ã¤jªº "¦Û°ÊÁÙ­ì" «ê¦n·|ªý¾×¤F³oºØ°¸º¸ªº»Ý¨D¡F

¥t¥~¨â¦ì¤j¤j¬ü¤¤¤£¨¬ªº¦@³qÂI¬O¡A­YCÄæ¨S¦³¤º®e¡A¨º»ò¹ïÀ³BÄ檺¿é¤J­­¨î¤]¯à§_¯àÀ°§Ú©¿²¤©O¡H   

¥H¤W¡AÁÂÁ¸ѴbªÌ¡I

­ã´£³¡ªL

¦^´_ 6# RCRG


If [B1] = 111 Then Exit Sub¡@
¢Ð¢°¿é¤J111¡A¤£°õ¦æ

¢ÑÄæªÅ®æ¡A¤£²z·|¡G
'If E(1, 2) = "" Then N = ""¡@§R¥h
If E <> N And E(1, 2) <> "" Then¡@¥[¤J§PÂ_

RCRG

¦^´_ 7# ­ã´£³¡ªL


«¢¡I­ì¨Ó "¸Ñ°£Äæ¦ì" ¤w¸g¦³À°§Ú³]­p¦bB1°Õ¡I­Ó¤H¤~²¨¾Ç²L¡A©êºp¡I
    ¸Õ¤F¥b¤Ñ¡A¬O¤£¬O­n§R°£¨â¦æ©O¡A¦]¬°¥u¬O§R°£¤@Äæ¦n¹³·|¦³°ÝÃD©O¡F

¹ï¤F¡I­ã¤jªº "¦Û°ÊÁÙ­ì" ®ÄªG¬OVBA¤¤ªº­þ¤@¦æ©O?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim E As Range, M, N
If [B1] = 111 Then Exit Sub¡@'§@°Ê¡eÃö³¬¡f¡@
For Each E In Target
¡@¡@If Not Application.Intersect(E, [B3:B79]) Is Nothing Then
¡@¡@¡@M = Application.Match(E(1, 2), [AA3:AA500], 0)
¡@¡@¡@If IsError(M) Then N = 3 Else N = 1
¡@¡@¡@' ³oÄæ¥i¥H§R°£¹ï§a  If E(1, 2) = "" Then N = ""
¡@¡@¡@' ³oÄæ¤]­n§R°£¹ï§a  If E <> N Then
If E <> N And E(1, 2) <> "" Then
¡@¡@¡@¡@MsgBox "¿é¤J¤º®e¿ù»~¡I±N¦Û°Ê­×¥¿¡ã¡ã¡@"
¡@¡@¡@¡@Application.EnableEvents = False
¡@¡@¡@¡@E = N
¡@¡@¡@¡@Application.EnableEvents = True
¡@¡@¡@End If
¡@¡@End If
Next
End Sub

RCRG

¦^´_ 7# ­ã´£³¡ªL

    Sorry¡I§Ú§ËÀ´­ã¤jªº¦r·N¤F¡I¬õ¦â¦r¬O§R¥hªº¡AÂŦâ¦r¬O¥[¶i¥hªº¡A¶Â¦â¦r¬O­ì¥»ªº¡A.....XD

RCRG

¦^´_ 6# RCRG


   ÁÂÁ­ã¤j«ü¾É¡AÅý§Ú¤]¥i¥H¤@¨Ã­×§ïGBKEE¤jªºVBA

'¤u§@ªíA ¼Ò²Õ
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim E As Range, M As String
    'Intersect ¤èªk ¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí¨â­Ó©Î¦h­Ó½d³ò­«Å|ªº¯x§Î½d³ò¡C
    For Each E In Target
        If Not Application.Intersect(E, [B3:B79]) Is Nothing Then
            'IF(C3="""","""",3^(1-COUNT(MATCH(C3,AA$3:AA$500,))))"
            M = "IF(" & E(1, 2).Address & "="""","""",3^(1-COUNT(MATCH(" & E(1, 2).Address & ",AA$3:AA$500,))))"
            If E <> Application.Evaluate(M) And E(1, 2) <> "" Then MsgBox E.Address(0, 0) & "= " & E & " ¶ñ¤J¸¹½X»P¹w³]¤£¦P"
        End If
    Next
End Sub