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

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

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

¥»©«³Ì«á¥Ñ RCRG ©ó 2015-11-12 11:35 ½s¿è

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


¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-11-12 14:28 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ RCRG ©ó 2015-11-12 16:10 ½s¿è

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

TOP

¦^´_ 3# RCRG


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

TOP

¦^´_ 2# lpk187


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

TOP

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

TOP

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

TOP

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

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

TOP

¸Õ¸Õ¬Ý:
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
test.gif

TOP

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


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

TOP

        ÀR«ä¦Û¦b : ¥¬¬I¦p¼½ºØ¡A¥HÅw³ß¤ß´þ¼íºØ¤l¡A¤~·|µoªÞ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD