- ©«¤l
- 2035
- ¥DÃD
- 24
- ºëµØ
- 0
- ¿n¤À
- 2031
- ÂI¦W
- 0
- §@·~¨t²Î
- Win7
- ³nÅ骩¥»
- Office2010
- ¾\ŪÅv
- 100
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2012-3-22
- ³Ì«áµn¿ý
- 2024-2-1
|
¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-6-27 06:13 ½s¿è
¦^´_ 4# Michelle-W
¤ñ¹ï·s¼W¸ê®Æ.rar (23.51 KB)
ªþ¤WÀɮרѧA´ú¸Õ¡C
¤ñ¹ï·s¼W¸ê®Æ(xls ®æ¦¡).rar (13.85 KB)
¦pªG§A¨ÌµM·Q¥Î쥻¤§«Å§i- If WorksheetFunction.CountIf(rng2, c.Value) = 0 Then
- .
- .
- End If
½Æ»s¥N½X ¥ç¥i¡A ¨ä»P¼Ó¤W (#6) ªº- If Not cts Is Nothing And ct2 Is Nothing Then
- .
- .
- End If
½Æ»s¥N½X ªíz¬O²§¦±¦P¤u¡C(¬Ù²¤¤F ct2 ªºÅܼƫŧi»Pµ¹¤©È (Assign Value) ªº³]©w )- Sub ¤ñ¹ï·s¼W2()
- Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr1 As Long, lr2 As Long
- Dim rng1 As Range, rng2 As Range, c As Variant, cts As Variant
-
- Set sh1 = Sheets(1) ' ¸ê®Æ
- Set sh2 = Sheets(2) ' List
- Set sh3 = Sheets(3) ' ¦¹¦¸·s¼W
-
- lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' ¸ê®Æ
- lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' List
-
- Set rng1 = sh1.Range("A1:A" & lr1) ' ¸ê®Æ
- Set rng2 = sh2.Range("A1:A" & lr2) ' List
-
- With sh3 ' ¦¹¦¸·s¼W
- .Cells.Clear
- ' .Range("A1") = "©m¦W"
- ' .Range("B1") = "¦~ÄÖ"
- ' .Range("C1") = "±B«Ã"
- .Range("A1").Resize(, 3) = Split("©m¦W,¦~ÄÖ,±B«Ã", ",")
- End With
-
- For Each c In rng1 ' ¸ê®Æ
- Set cts = rng1.Find(c.Value, , LookIn:=xlValues)
-
- If WorksheetFunction.CountIf(rng2, c.Value) = 0 Then
- sh3.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = sh1.Rows(cts.Row).Value ' ¦¹¦¸·s¼W
- End If
- Next
- End Sub
½Æ»s¥N½X §í©Î¬O®Ú¥»¤£¨Ï¥Î cts¡Bct2 ªº¨âÓÅܼƫŧi»Pµ¹¤©È (Assign Value) ªº³]©w- Sub ¤ñ¹ï·s¼W3()
- Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lr1 As Long, lr2 As Long
- Dim rng1 As Range, rng2 As Range, c As Variant
-
- Set sh1 = Sheets(1) ' ¸ê®Æ
- Set sh2 = Sheets(2) ' List
- Set sh3 = Sheets(3) ' ¦¹¦¸·s¼W
-
- lr1 = sh1.Cells(Rows.Count, 1).End(xlUp).Row ' ¸ê®Æ
- lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row ' List
-
- Set rng1 = sh1.Range("A1:A" & lr1) ' ¸ê®Æ
- Set rng2 = sh2.Range("A1:A" & lr2) ' List
-
- With sh3 ' ¦¹¦¸·s¼W
- .Cells.Clear
- ' .Range("A1") = "©m¦W"
- ' .Range("B1") = "¦~ÄÖ"
- ' .Range("C1") = "±B«Ã"
- .Range("A1").Resize(, 3) = Split("©m¦W,¦~ÄÖ,±B«Ã", ",")
- End With
-
- For Each c In rng1 ' ¸ê®Æ
- If WorksheetFunction.CountIf(rng2, c.Value) = 0 Then
- sh3.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3) = sh1.Rows(c.Row).Value ' ¦¹¦¸·s¼W
- End If
- Next
- End Sub
½Æ»s¥N½X ¨äµ²ªG¤´¬O¤@Pªº¡C
¦¹ºÝµø§AÓ¤H¼¶¼gªº¸gÅç¡B²ßºD»PÅÞ¿è«ä¦Ò¡C
¤ñ¹ï·s¼W¸ê®Æ(¦^Âkì©l).rar (16.91 KB)
|
|