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

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

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

±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

ÁÂÁÂ^^

1.jpg
2012-11-1 13:37


Zip32_10108.rar (897.35 KB)

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

TOP

¦^´_ 2# GBKEE


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

TOP

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

TOP

¦^´_ 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ÁÂÁ«ü±Ð^^


  
001.jpg
2012-11-1 16:50


  
002.jpg
2012-11-1 16:50


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

TOP

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

TOP

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

TOP

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

TOP

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

TOP

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

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD