[µ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

lpk187

¦^´_ 1# RCRG
Msgboxªº³Ì¤j¦r¤¸¦h¤Ö¡A¤£ª¾¹D¡A¤£¹L¨ÌVBE»¡©ú:³Ì¤jªø«×¤j¬ù¬O1024­Ó¦r¤¸¡A¥Ñ¨Ï¥Î¦r¤¸ªº¼e«×¨M©w¡C¦pªG prompt ¶W¹L¤@¦æ¡A±z¥i¥H¦b¨C¤@¦æ¤§¶¡¥Î´_¦ì¦r¤¸(Chr(13))¡B´«¦æ¦r¤¸ (Chr(10)) ©Î¬O´_¦ì¦r¤¸»P´«¦æ¦r¤¸ªº²Õ¦X (Chr(13) & Chr(10)) ¨Ó°µ°Ï¹j¡C
¬O¤£¬O´«¦æ«á¥i¥H¼W¥[¡A¤£½T©w¡C½Ð¦Û¦æ°Ñ¦ÒVBE»¡©ú

1. Sub «ö¶s1_Click()
   
2. Dim Rng As Range
   
3. With Sheets("B")
   
4.     For Each Rng In .Range("C2:CC1000")
   
5.         If Rng <> "" Then
   
6.             Set da = Sheets("A").Range("c3:c79").Find(Rng.Value, LookAt:=xlWhole, SearchDirection:=2)
   
7.             If da Is Nothing Then
   
8.                 aa = aa & Rng.Value & ","
   
9.             Else
   
10.                 Set da = Nothing
    
11.             End If
    
12.         End If
    
13.     Next
    
14.     aa = Left(aa, Len(aa) - 1)
    
15.     MsgBox aa
    
16. End With
    
17. End Sub

½Æ»s¥N½X

RCRG

¦^´_ 2# lpk187


    ¡@ÁÂÁÂlpk187¤jªº¸Ñµª¡A§¹¥þ²Å¦X§Ú©Ò­nªº(C2:C1000½d³ò¿ù¤F¡A§Ú¦Û¦æ­×¥¿¤F)¡F
¹ï¤F¡AMsgbox§Ú¦pªG«e­±­n¦b¥[¨Ç¤¤¤å "¬Û²§¸¹½X¦p¤U¡GL777779,L787878C,L989598"¡A³o¼Ë­n¦p¦ó­×§ï©O?

lpk187

¦^´_ 3# RCRG


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

RCRG

¦^´_ 2# lpk187


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

­ã´£³¡ª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

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

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

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

RCRG

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


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