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

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

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

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

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-11-29 16:58 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-11-29 17:41 ½s¿è

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-11-29 17:03 ½s¿è

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

TOP

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

TOP

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

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD