[µo°Ý] ¦p¦ó§Q¥ÎVBA¤@Áä ¦Û°Ê¤ñ¹ï¤u§@ªíA & BªºCÄæ¬Û²§¸¹½X¡H

RCRG

¤@Áä¦Û°Ê¤ñ¹ï¨â¤u§@ªí¬Û²§¸¹½X.rar (12.97 KB)

¤U¸ü¦¸¼Æ: 22

2015-11-12 11:31

¤U¸ü (22.07 KB)

2015-11-12 11:35

¤U¸ü (11.1 KB)

2015-11-12 11:34

­ã´£³¡ªL

­É¥Î¶WªOªº¡e°}¤¤°}¡f¡G
Sub TEST()
Dim xD, SS, S
Set xD = CreateObject("Scripting.Dictionary")
For Each SS In Array([B!C2:C1000], [C!C2:C1000])
For Each S In SS
¡@¡@If S <> "" And Not xD.Exists(S & "") Then xD(S & "") = ""
Next: Next
For Each S In [A!C3:C79]
¡@¡@If xD.Exists(S & "") Then xD.Remove S & ""
Next
If xD.Count Then MsgBox "¬Û²§¸¹½X¦p¤U¡G" & Join(xD.keys, ",") Else MsgBox "µL¬Û²§"
End Sub

GBKEE

¦^´_ 10# RCRG
¸Õ¸Õ¬Ý VBA ÁÙ¦³¨ä¥¦»yªk¥i¼g

1. Option Explicit
   
2. Sub EX()
   
3.     Dim Rng, AR, T, E
   
4.     Rng = Array([A!C3:C79], [B!C2:C79]) '[C!C2:C79],[D!C2:C79],[E!C2:C79].....¥i¥[¤J³\¦h½d³ò
   
5.     For Each E In Rng
   
6.         AR = Application.Transpose(E.Value)
   
7.         T = T & "," & Join(AR, ",")
   
8.     Next
   
9.     AR = Split(T, ",")
   
10.     T = ""
    
11.     For Each E In AR
    
12.         If E <> "" Then
    
13.         'Filter ¨ç¼Æ ¶Ç¦^¤@­Ó±q¹s¶}©lªº°}¦C¡A¸Ó°}¦C¥]§t°ò©ó«ü©w¿z¿ï·Ç«hªº¤@­Ó¦r¦ê°}¦Cªº¤l¶°¡C        '
    
14.            If UBound(Filter(AR, E, True)) = 0 Then '°}¦C¸Ì¥u¦³¤@­Ó¤¸¯À
    
15.                 T = T & IIf(T <> "", ",", "") & E
    
16.            End If
    
17.         End If
    
18.     Next
    
19.     If T <> "" Then
    
20.         T = "¬Û²§¸¹½X¦p¤U¡G" & vbLf & Replace(T, ",", vbLf)
    
21.     Else
    
22.         T = "¬dµL¬Û²§¸¹½X"
    
23.     End If
    
24.     MsgBox T
    
25. End Sub

½Æ»s¥N½X

RCRG

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


    ÁÂÁ­ã¤j¦A¦¸¸ÑÃD¡AÅý¨£¯º¤F...XD

yen956

¸Õ¸Õ¬Ý:
1. ´¡¤JUserForm1, ¨Ã³]©w Caption ªºÄݩʬ°"¤ñ¹ïµ²ªG"
2. ¦b UserForm1 ¤¤´¡¤J Label1, ListBox1 ¤Î CommandButton1
3. ³]©w UserForm1.CommandButton1 ªº Caption Äݩʬ°"½T©w"
4. Double Click UserForm1.CommandButton1, ¦b½s¿èµøµ¡¤¤Áä¤J¤U¦C VBA code:

1. Private Sub CommandButton1_Click()
   
2.         Unload Me
   
3.     End Sub

½Æ»s¥N½X

5. Â÷¶}VBA½s¿èµøµ¡¦^¨ì sheetA, ¦b sheetA ¤¤´¡¤JCommandButton1,
    ¨Ã³]©w Caption ªºÄݩʬ°"«ö§Ú¤ñ¹ï"
6. Double Click CommandButton1, ¦b½s¿èµøµ¡¤¤Áä¤J¤U¦C VBA code:

1. Private Sub CommandButton1_Click()
   
2.     Dim rngA As Range, rngB As Range, rngC As Range
   
3.     Dim cel As Range, foundCel As Range
   
4.     Dim cntLB As Integer, R As Integer
   
5.     Set rngA = [A!C3:C79]
   
6.     Set rngB = [B!C3:C1000]
   
7.     Set rngC = [C!C3:C500]
   
8.     '
   
9.     '¥ý¤ñ¹ïrngB ¤ÎrngC, ¨Ã±N¬Û²§¸¹½X¦s¨ì¼È¦s°Ï[B!D:D]
   
10.     '¦A­«³] rngB, ¶i¦Ó¤ñ¹ïrngB ¤ÎrngA
    
11.     '
    
12.     UserForm1.ListBox1.Clear       '²M°£ListBox
    
13.     [B!D:D] = ""     '²M°£¼È¦s°Ï
    
14.     R = 1
    
15.     For Each cel In rngC
    
16.         If cel <> "" Then
    
17.             Set foundCel = rngB.Find(cel, LookAt:=xlWhole, SearchDirection:=2)
    
18.             
    
19.             'foundCel Is Nothing ªí¥Ü¨S§ä¨ì, §Y rngCªºcel »P rngB ¤£­«ÂÐ
    
20.             If foundCel Is Nothing Then
    
21.                 R = R + 1
    
22.                 Sheets("B").Cells(R, 4) = cel '±N rngCªºcel ¥[¨ì ¼È¦s°Ï
    
23.             End If
    
24.         End If
    
25.     Next
    
26.    
    
27.     Set rngB = [B!C3:D1000]      '¦A­«³] rngB
    
28.     For Each cel In rngB
    
29.         If cel <> "" Then
    
30.             Set foundCel = rngA.Find(cel, LookAt:=xlWhole, SearchDirection:=2)
    
31.             
    
32.             'foundCel Is Nothing ªí¥Ü¨S§ä¨ì, §Y rngBªºcel »P rngA ¤£­«ÂÐ
    
33.             If foundCel Is Nothing Then
    
34.                 UserForm1.ListBox1.AddItem cel    '±N rngBªºcel ¥[¨ì ListBox1
    
35.             End If
    
36.         End If
    
37.     Next
    
38.     cntLB = UserForm1.ListBox1.ListCount
    
39.     If cntLB = 0 Then
    
40.         MsgBox "§ä¤£¨ì¬Û²§¸¹½X!!", vbCritical
    
41.     Else
    
42.         UserForm1.Label1.Caption = "¦@¦³" & cntLB & "µ§¬Û²§¸¹½X," & Chr(10) _
    
43.                  & "¦p¤U©Ò¦C¡G"
    
44.         UserForm1.Show
    
45.     End If
    
46. End Sub

½Æ»s¥N½X

¤U¸ü (213.54 KB)

2015-11-14 15:44

­ã´£³¡ªL

¦^´_ 7# RCRG


For Each ST In [B!C2:C1000]
¡@¡@MH = 0
¡@¡@If ST <> "" Then MH = Application.Match(ST, [A!C2:C79], 0)
¡@¡@If IsError(MH) Then
¡@¡@¡@¡@If InStr("," & TT & ",", "," & ST & ",") = 0 Then TT = TT & "," & ST
¡@¡@End If
Next
If TT = "" Then MsgBox "µL¬Û²§" Else MsgBox "¬Û²§¸¹½X¦p¤U¡G" & Mid(TT, 2)

¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
¢°¡DInStr("," & TT & ",", "," & ST & ",")
¡@¡@¤ñ¹ï¤å¦r­n¥H¡e³r¸¹¡f«e«á¥]ÂСA§_«h¥H L111222 ¤ñ¹ï L111222A ·|»~§P¡I
¡@¡@¥H ,L111222, ¤ñ¹ï ,L111222A, ´N¨S°ÝÃD¡ã³o»P¨Ï¥Î Find ¨ç¼Æ¦P²z¡ã
¡@
¢±¡DIf TT = "" Then MsgBox "µL¬Û²§" Else MsgBox "¬Û²§¸¹½X¦p¤U¡G" & Mid(TT, 2)
¡@¡@If ~~ Then ~~ Else ~~, ³o¬O³Ì²³æ°ò¥»ªº§PÂ_

­Y¾Ç excel ¤w¦³¤@¬q®É¶¡¡A ¤W­±ªºµ{¦¡½XÀ³³£¥i¥H²z¸Ñªº¡I

RCRG

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

1. ¦pªG§Ú­n¦A¦h¤ñ¹ï¤@­Ó¤u§@ªíC(¦ý½d³ò¥u¦³¨ìC2:C500)¡A§Ú¦Û¦æÀH«K¶Ã­×§ï¦p¤UÀ³¸Ó¨S°ÝÃD§a(§Ú¦Û¤v´ú¸Õ°_¨Ó¬OOK°ÕQQ)
2. ¥t¥~¬Û²§ªº¸¹½X¦pªG¬O¤@¼Ëªº¥i¥H¥uÅã¥Ü¤@­Ó¶Ü?¦pªG¦³¨â­ÓL123456¡A¼u¥Xµøµ¡´N·|¶]L123456,L123456³o¼Ë¡A¦Ó§Ú¥u·Q¶]¤@­Ó¥X¨Ó´N¦n¡C
3. ¦pªG¤ñ¹ïªº¤u§@ªíB¨S¦³¤º®e©Î¨S¦³¬Û²§¸¹½X¡A«ö¤U¥hÁöµM¤£·|¦³°»¿ùµøµ¡¤F¡A¦ý¦ü¥G¤]¨S¦³¥ô¦óµøµ¡¥X²{´£¿ô¡A·|Åý¤H»~¥H¬°¦³¦b¤ñ¹ï¶Ü?¤£ª¾¯à§_À°§Ú¼u¥X "¸ê®Æ¥¿±`" ªº¼u¥Xµøµ¡¦r²´¡C


    Sub TEST()
Dim MH, ST, TT$
For Each ST In [B!C2:C1000]
¡@¡@MH = 0
¡@¡@If ST <> "" Then MH = Application.Match(ST, [A!C2:C79], 0)
¡@¡@If IsError(MH) Then TT = TT & "," & ST
Next

For Each ST In [C!C2:C500]
¡@¡@MH = 0
¡@¡@If ST <> "" Then MH = Application.Match(ST, [A!C2:C79], 0)
¡@¡@If IsError(MH) Then TT = TT & "," & ST
Next
If TT <> "" Then MsgBox "¬Û²§¸¹½X¦p¤U¡G" & Mid(TT, 2)

End Sub

­ã´£³¡ªL

Sub TEST()
Dim MH, ST, TT$
For Each ST In [B!C2:C1000]
¡@¡@MH = 0
¡@¡@If ST <> "" Then MH = Application.Match(ST, [A!C2:C79], 0)
¡@¡@If IsError(MH) Then TT = TT & "," & ST
Next
If TT <> "" Then MsgBox "¬Û²§¸¹½X¦p¤U¡G" & Mid(TT, 2)
End Sub

RCRG

¦^´_ 2# lpk187


    µo²{¤@­Ó²§±`¡A´N¬O¦pªG¤u§@ªíBªºCÄæ¦ì¨S¸ê®Æ¥i¥H¤ñ¹ïªº¸Ü¡A·|¼u¥X¤@­Ó¿ù»~µøµ¡©O¡A¯à§âªÅ¥Õ¤º®eµø¬°¥¿±`¶Ü?

lpk187

¦^´_ 3# RCRG


    MsgBox "¬Û²§¸¹½X¦p¤U¡G" & aa