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

[µo°Ý] ¦p¦ó³]©w·í¦bªí³æ1¿é¤JA­È±a¥Xªí³æ2©Ò¦³A­Èªº¼Æ¾Ú

[µo°Ý] ¦p¦ó³]©w·í¦bªí³æ1¿é¤JA­È±a¥Xªí³æ2©Ò¦³A­Èªº¼Æ¾Ú

½Ð°ÝExcel¥i¥H§Ë¦¨¸òAccess¤@¼Ë
¦bªí³æ1¿é¤JA­È±a¥Xªí³æ2©Ò¦³A­Èªº¼Æ¾Ú¶Ü!?
ÀɮסG
http://sync.hamicloud.net/_oops/k_k013160/tn1

¥»©«³Ì«á¥Ñ yen956 ©ó 2014-3-19 15:57 ½s¿è

¦^´_ 1# 013160
³o¬O2003ª©, §Aªº¬O2007ª©, À³¦³§ó²³æª©
¦ýÀ³¤´¥i¥Î, ¸Õ¸Õ¬Ý:
  1. 'Sheet1
  2. Option Explicit
  3. '¦Ò¼{¨ì¡i½s¸¹¡j¬Û·íªø(ªø¹F12¦ì¼Æ),¬G¥Î¤U©Ô¦¡¿ï³æ¿é¤J¡i½s¸¹¡j
  4. Private Sub ComboBox1_Change()
  5.     Dim sh1, sh2 As Object
  6.     Dim i, endRow, cnt As Integer
  7.     Set sh1 = Sheets("Sheet1")
  8.     Set sh2 = Sheets("Sheet2")
  9.     If ComboBox1 = "" Then Exit Sub
  10.    
  11.     '²M°£­ì¦³¸ê®Æ
  12.     sh1.[B2].Resize(2000, 2) = ""
  13.    
  14.     '¨ú±o ½s¸¹ ³Ì«á¤@¦Cªº¦C¸¹
  15.     endRow = sh2.[D2000].End(xlUp).Row
  16.     cnt = 1
  17.     For i = 2 To endRow
  18.         If sh2.Cells(i, 4) = --ComboBox1 Then
  19.             cnt = cnt + 1
  20.             sh1.Cells(cnt, 2) = sh2.Cells(i, 1)  '½Æ»s ­¶¼Æ
  21.             sh1.Cells(cnt, 3) = sh2.Cells(i, 6)  '½Æ»s ¾l¼Æ
  22.         End If
  23.     Next
  24. End Sub
  25. '
  26. 'Sheet2
  27. Option Explicit
  28. '¦pªG Sheet2 ªº¡i½s¸¹¡j¦³¼W´î®É,¥iÂI¿ï Sheet2,
  29. '¥Î¥HIJ°Ê¥»µ{§Ç, ¶i¦æ¸ê®Æ­«¾ã
  30. Private Sub Worksheet_Activate()
  31.     Dim i, endRow As Integer
  32.    
  33.     '2007ª©¦ü¥i ±N¡i¿z¿ï¡j»P¡i±Æ§Ç¡j¤@¨Ö³B²z
  34.     '¥i±¤§Úªº¬O 2003ª©, ¥u¯à¤À¶}³B²z
  35.     '¥Î¡i¶i¶¥¿z¿ï¡j±N¡i½s¸¹¡j¿z¿ï¨ì ÄæI, ¨Ã¥h°£¡i­«Âнs¸¹¡j
  36.     Range("D1:D2000").AdvancedFilter Action:=xlFilterCopy, _
  37.               CriteriaRange:=Range("D1:D2000"), _
  38.               CopyToRange:=Range("I1"), Unique:=True
  39.    
  40.     endRow = [I2000].End(xlUp).Row
  41.     '±N¿z¿ïµ²ªG, ½Æ»s¨ì ÄæJ
  42.     [J1].Resize(2000, 1) = ""
  43.     For i = 1 To endRow
  44.         Cells(i, 10) = Cells(i, 9)
  45.         Cells(i, 9) = ""
  46.     Next
  47.    
  48.     '±N ÄæJ «ö¤É¾­±Æ§Ç, ¨Ã±N®æ¦¡³]¬° "0000000000000"
  49.     Range("J1:J2000").Sort Key1:=Range("J1"), _
  50.           Order1:=xlAscending, Header:=xlYes
  51.     Range("J1:J2000").NumberFormatLocal = "0000000000000"
  52.    
  53.     '­«·s©w¸q ¦WºÙ "x" ªº½d³ò, ¨Ñ Sheet1 ªº ComboBox1 ¥Î
  54.     endRow = [J2000].End(xlUp).Row
  55.     ActiveWorkbook.Names("x").Delete
  56.     ActiveWorkbook.Names.Add Name:="x", _
  57.           RefersToR1C1:="=Sheet2!R2C10:R" & endRow & "C10"
  58. End Sub
½Æ»s¥N½X
¿é¤J½s¸¹.7z
http://www.mediafire.com/download/4bw6wen76mga98f/¿é¤J½s¸¹.7z

TOP

¦^´_ 2# yen956


    ÁÂÁÂyen956ªº§iª¾¡A°ÝÃD¤w¸Ñ¨M¡C

TOP

¦^´_ 2# yen956
¨Ó¤£¤Î½s¿è¸É¤W§ÚªººÃ°Ý
  '±N¿z¿ïµ²ªG, ½Æ»s¨ì ÄæJ     -----------------½Æ»s¨ìÄæJ¦ý§Ú¨S¬Ý¨ìJÄ榳Åã¥Ü¥X¥ô¦ó¸ê®Æ!?
    [J1].Resize(2000, 1) = ""-------2000¬O«ü2000µ§¸ê®Æ¶Ü!?
    For i = 1 To endRow
        Cells(i, 10) = Cells(i, 9)
        Cells(i, 9) = ""
    Next

TOP

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD