[µo°Ý] ¿z¿ï?ÃöÁä¦r?¬d¸ß?

emma

±z¦n¡A¦p¹Ï¡A¦pªG·Q²¤Æ¦Û°Ê¿z¿ïªº¨BÆJ(¿ï¡u¦Û­q¡v¦A¿é¤J­n¬d¸ßªº­Èµ¥©ó¡u10058¡v¡A
¦pªG¬O¥u­n¦³¥]§t100ªº¸Ü­n±ø¥ó¦A¿ï¾Ü¡u¥]§t¡vªº³o¨Ç¨BÆJ¬Ù²¤)¡Aª½±µ¦b
B1ªº¦a¤è¿é¤J­n¿z¿ï©Î¬d¸ßªºÃöÁä¦r¡A«öENTER©ÎÂ÷¶}¿é¤JªºÀx¦s®æ«á¡A
¥i¥H°õ¦æ¥X¦p¤U¹Ïªºµ²ªG¡A½Ð°Ý¥Î¦óºØ¤è¦¡»s§@³Ì¦³®Ä²v

ÁÂÁÂ^^

¤U¸ü (71.54 KB)

2012-11-1 13:37

Zip32_10108.rar (897.35 KB)

¤U¸ü¦¸¼Æ: 22

2012-11-1 13:42

GBKEE

¦^´_ 1# emma
¥u­n¦³¥]§t100ªº¸Ü­n±ø¥ó¦A¿ï¾Ü  ³o¬O¦r¦êªº¿z¿ï¤è¦¡
Zip CodeÄæ ªº¸ê®Æ¬O¼Æ¦r,­n·í¦r¦ê¥Î ¶·¥þ³¡ªº¼Æ¦r«e ' ¦p '10058 ¨t²Î·|±Nµø¬°¤å¦r  ,¤~¥i¥H¥Î¦r¦êªº¿z¿ï¤è¦¡/
¸Õ¸Õ¬Ý
Sheet1¤u§@ªí ¼Ò²Õªºµ{¦¡½X

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     If Target.Address(0, 0) = "B1" Then
   
4.         Range("A3").AutoFilter Field:=1, Criteria1:=Target & "*"
   
5.     End If
   
6. End Sub

½Æ»s¥N½X

emma

¦^´_ 2# GBKEE


    ÁÂÁÂGBKEEª©¤j¡A¥i¥H¥¿±`°õ¦æ¦¨¥\¡A¦ý¤£¬O«Ü²z¸Ñµ{¦¡ªº»yªk?
    ¡õ½Ð°Ý³o¥y¬O¤°»ò·N«ä?
    Range("A3").AutoFilter Field:=1, Criteria1:=Target & "*"

GBKEE

¦^´_ 3# emma
AutoFilte: ¦Û°Ê¿z¿ï ¥i¬Ývbaªº»¡©ú

emma

¦^´_ 4# GBKEE


    ÁÂÁÂGBKEEª©¤j¡A§Ú·|§V¤O¬ã¨s¾Ç²ß³o¨Ç­¯¥Íªº»yªk~"~¡A§Ú¥i¥H¦A¥t¥~¦V±z½Ð±Ð¤@­Ó°ÝÃD¶Ü? >///<
    ½Ð°Ý¨Ï¥ÎªÌ¯àÂI¿ï¬Y¦C«á¡A±N¦¹¦CªºÄæ¦ì±a¨ìSheet2¸Ì¦p¤U¹Ï©Ò¥Üªº¦ì¸m¤¤¡A
    ÂI¿ïªº¶¶§Ç¤£­«­n¡A¦ý¬OÂI¹L¤@¦¸ªº¨º¤@¦C¸ê®Æ´NµLªk¦bSheet2¥X²{­«½Æªº²Ä¤G¦¸(¤]´N¬O¨Cµ§¸ê®Æ¥u¯à³Q·s¼W°ß¤@¤@¦¸¨ìSheet2¸Ì)
    «ö²M°£ªº«ö¶s«á´N·|²bªÅSheet2¸Ìªº­È¨Ãªð¦^¨ìSheet1ªº¤u§@ªí¤¤
    ¤£¾åªº³o¼Ëªº±Ô­z¦³¨S¦³¤£²M·¡ªº¦a¤è¡AÁÂÁ«ü±Ð^^


  

¤U¸ü (132.53 KB)

2012-11-1 16:50

  

¤U¸ü (51.89 KB)

2012-11-1 16:50

   VBA¾Ç²ßÀ³¥Î10111.rar (853.87 KB)

¤U¸ü¦¸¼Æ: 21

2012-11-1 16:59

GBKEE

¦^´_ 5# emma
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     Dim Target_Row As String
   
4.     If Target.Address(0, 0) = "D1" Then
   
5.         Range("C3").AutoFilter Field:=3, Criteria1:="*" & Target & "*"
   
6.     ElseIf Target.Address(0, 0) = "B1" Then
   
7.         Range("A3").AutoFilter Field:=1, Criteria1:="*" & Target & "*"
   
8.     End If
   
9. End Sub
   
10. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
11.     Dim Target_Row As String, xi As Integer, xi_Row As String
    
12.     If Not Application.Intersect(Range("A4", Range("A4").End(xlDown)), Target) Is Nothing Then
    
13.         'Intersectª«¥ó
    
14.         Target_Row = Join(Application.Transpose(Application.Transpose(Target.Resize(, 5))), ",")
    
15.         'Join ¨ç¼Æ:¶Ç¦^¤@­Ó¦r¦ê¡A¸Ó¦r¦ê¬O³z¹L³sµ²¬Y­Ó°}¦C¤¤ªº¦h­Ó¤l¦r¦ê¦Ó«Ø¥ßªº¡C
    
16.         xi = 7
    
17.         Do While Sheets("sheet2").Cells(xi, 1) <> ""
    
18.            xi_Row = Join(Application.Transpose(Application.Transpose(Sheets("sheet2").Cells(xi, 1).Resize(, 5))), ",")
    
19.            If xi_Row = Target_Row Then Exit Sub
    
20.            xi = xi + 1
    
21.         Loop
    
22.         Sheets("sheet2").Cells(xi, 1).Resize(, 5) = Split(Target_Row, ",")
    
23.         'Split ¨ç¼Æ: ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
    
24.     End If
    
25. End Sub

½Æ»s¥N½X

emma

¦^´_ 6# GBKEE


    «z¡A¯uªº¨S·Q¨ì¥i¥H°µ±o¥X¨Ó³o¼Ëªº®ÄªG¡AÁÂÁÂGBKEEª©¤j¡A§Ú­n¦n¦n§V¤O¿Ä·|³e³q¤@¤U¡A¤Ó¶}¤ß¤F¡A«D±`ªº·PÁ±z^^

emma

¦^´_ 6# GBKEE


    GBKEEª©¤j¡A¤£¦n·N«ä¡A§Ú¤S·Q¨ì¥t¤@­Ó°ÝÃD¡A¦pªG±a¨ìSheet2¤¤ªº¤£¬O³sÄòªºÄæ¦ì©O?
    ¨Ò¦p¥u¦³²Ä¤@Äæ¡B²Ä¤TÄæ¡B²Ä¤­Äæ
    Zip Code        ¡BArea¡BScope
    ­n¦p¦ó§ó§ï©O?

    ³Â·Ð±z¤F¡AÁÂÁÂ^^

GBKEE

¦^´_ 8# emma

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Dim Target_Row As String, xi As Integer, xi_Row As String
   
3.     If Not Application.Intersect(Range("A4", Range("A4").End(xlDown)), Target) Is Nothing Then
   
4.         'Intersectª«¥ó
   
5.         Target_Row = ²Ä¤@Äæ & "," & ²Ä¤TÄæ & "," & ²Ä¤­Äæ    '³o¸Ì¦Û¦æ­×§ï ³s±µ¤£³sÄò¦ì¸m
   
6.         xi = 7
   
7.         Do While Sheets("sheet2").Cells(xi, 1) <> ""
   
8.            xi_Row = Join(Application.Transpose(Application.Transpose(Sheets("sheet2").Cells(xi, 1).Resize(, 3))), ",")
   
9.            If xi_Row = Target_Row Then Exit Sub
   
10.            xi = xi + 1
    
11.         Loop
    
12.         Sheets("sheet2").Cells(xi, 1).Resize(, 3) = Split(Target_Row, ",")
    
13.         'Split ¨ç¼Æ: ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
    
14.     End If
    
15. End Sub

½Æ»s¥N½X

emma

¦^´_ 9# GBKEE


    ÁÂÁÂGBKEEª©¤jªº­@¤ß«ü¾É¡A¨Ì±z´`´`µ½»¤ªº¤è¸ÕÅý§Ú¦Û¦æ§ä¥X§Ú­nªºµª®×¤F¡A«D±`·PÁ±zªº¥Î¤ß^^

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Dim Target_Row As String, xi As Integer, xi_Row As String
   
3.     If Not Application.Intersect(Range("A4", Range("A4").End(xlDown)), Target) Is Nothing Then
   
4.        'Intersectª«¥ó
   
5.         Target_Row = Target(, 1) & "," & Target(, 3) & "," & Target(, 5)    '³o¸Ì¦Û¦æ­×§ï ³s±µ¤£³sÄò¦ì¸m
   
6.         xi = 7
   
7.         Do While Sheets("sheet2").Cells(xi, 1) <> ""
   
8.         xi_Row = Join(Application.Transpose(Application.Transpose(Sheets("sheet2").Cells(xi, 1).Resize(, 3))), ",")
   
9.         If xi_Row = Target_Row Then Exit Sub
   
10.            xi = xi + 1
    
11.         Loop
    
12.         Sheets("sheet2").Cells(xi, 1).Resize(, 3) = Split(Target_Row, ",")
    
13.         'Split ¨ç¼Æ: ¶Ç¦^¤@­Ó³¯¦C¯Á¤Þ±q¹s¶}©lªº¤@ºû°}¦C¡A¥¦¥]§t«ü©w¼Æ¥Øªº¤l¦r¦ê¡C
    
14.     End If
    
15. End Sub

½Æ»s¥N½X

³o¼Ë°õ¦æ¥Xªº¬O§Ú·Q­nªºµ²ªG¡AÀ³¸Ó¨S¿ù§a?!