[µo°Ý] ±Æ§Ç«á§R°£¯S©wÄd¦ì

billchenfantasy

¤U¸ü (68.14 KB)

2013-1-22 15:53

§e¤W¸ê®Æ­¶­±¡A½Ð°Ý¦pªG§Ú­n¥ý¥Hland_no_m©Mland_no_c¨âÄd¬°·Ç°µ±Æ§Ç¤§«á«O¯d¥H¤U³o¤E­ÓÄæ¦ì¨ä¥L§R°£¸Ó«ç»ò¼¶¼gVBA©O~·PÁÂ
(section) (SC) (LANDUSE)  (PUBNO)        (OPTION)        (METHOD)        (MUPLAN)        (DUPLAN)        (ORG_FID)

¤U¸ü (20.85 KB)

2013-1-22 16:05

stillfish00

¦^´_ 12# billchenfantasy
®¦ ¸Õ¤F¤@¤U  #10 ªº¨â¦æ¶¶§Ç½Õ´«
µ²ªGÀ³¸Ó¸ò #11 ¤@¼Ë

billchenfantasy

@@¬Oªº¨C±iÄæ¦ì®æ¦¡³£¬O¤@¼Ëªº¡A©Ò¥Hland_no_m, land_no_c ¨C±iªí³£¦³¡A¥u¦³¤Ö¼Æ¤~¦³¦h¤@¦æªºª¬ªp¤~·|·Q»¡«ü©wÄæ¦ì¡A·PÁ±zªº¦A«×¨ó§U

stillfish00

¦^´_ 10# billchenfantasy
land_no_m, land_no_c ¨C±iªí³£¦³¶Ü?

1.     If Not (IsError(Application.Match("land_no_m", .Rows(1), 0)) Or _
   
2.             IsError(Application.Match("land_no_c", .Rows(1), 0))) Then
   
3.         .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_m", .Rows(1), 0)), _
   
4.                                     Order1:=xlAscending, _
   
5.                                     Key2:=.Columns(Application.Match("land_no_c", .Rows(1), 0)), _
   
6.                                     Order2:=xlAscending, _
   
7.                                     Header:=xlYes
   
8.     Else
   
9.         MsgBox .Sheets(i).Name & " : Sorting field not found."
   
10.     End If

½Æ»s¥N½X

billchenfantasy

¹J¨ì°ÝÃD¤F@@¦pªG»Ý­n¥Hland_no_m¬°KEY1»Pland_no_c¬°KEY2¥h°µ±Æ§Ç¥H¤U±Æ§Çªºµ{§Ç¸Ó¦p¦ó­×§ï£z

If Not IsError(Application.Match("land_no_m", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_m", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes

  If Not IsError(Application.Match("land_no_c", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_c", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes

billchenfantasy

·P¿E~µù¸Ñ¸Ô²Ó~·Pı¤W¤F¤@½Ò@@ÁÂÁ±zªº¸Ñµª

stillfish00

¦^´_ 7# billchenfantasy

1. Private Sub CommandButton1_Click()
   
2. Dim Source, f
   
3. Dim rng As Range
   
4. 
   
5. '¥i¿ï¾Ü¦h­ÓÀÉ®×
   
6. Source = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", _
   
7.                                     MultiSelect:=True)
   
8. If TypeName(Source) = "Boolean" Then If Source = False Then Exit Sub
   
9. 
   
10. For Each f In Source
    
11. '¶}±ÒÀÉ®×/¬¡­¶Ã¯
    
12. With Workbooks.Open(f)
    
13.     '¹ï©Ò¦³¤u§@ªí
    
14.     For i = 1 To ActiveWorkbook.Sheets.Count
    
15.         '½Æ»s¤u§@ªí¨ì¥»¬¡­¶Ã¯
    
16.         .Sheets(i).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
17.         '¥»¬¡­¶Ã¯¤¤¸Ó¤u§@ªí
    
18.         With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
19.            '¨Ìland_no_m, land_no_cÄæ±Æ§Ç
    
20.             If Not IsError(Application.Match("land_no_m", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_m", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes
    
21.             If Not IsError(Application.Match("land_no_c", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_c", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes
    
22.             
    
23.             '§ä¥X¤£²Å¦XªºÄæ
    
24.             For j = 1 To .[A1].CurrentRegion.Columns.Count
    
25.                 If IsError(Application.Match(.Cells(1, j).Value, Array("section", "SC", "LANDUSE", "PUBNO", "OPTION", "METHOD", "MUPLAN", "DUPLAN", "ORG_FID"), 0)) Then
    
26.                     If rng Is Nothing Then Set rng = .Columns(j) Else Set rng = Union(rng, .Columns(j))
    
27.                 End If
    
28.             Next j
    
29.             '§R°£
    
30.             .Range(rng.Address).Delete shift:=xlToLeft
    
31.             Set rng = Nothing
    
32.         End With
    
33.     Next i
    
34.     'Ãö³¬ÀÉ®×
    
35.     .Close
    
36. End With
    
37. Next f
    
38. End Sub

½Æ»s¥N½X

billchenfantasy

·PÁÂstillfish00 ³o¹ï§Ú³oµæ³¾¨Ó»¡¡A¬Ý°_¨Ó²ª½¬OÅÜÅ]³N¡A·P®¦¡A¬O±zªº¶×¤J¤è¦¡¤ñ¸û¦X²z¡A
.Range("A:C,E:I,K,Q").Delete Shift:=xlToLeft¬Oª½±µ¿ï©wÄæ¦ì
³Ì«á¤@­Ó°ÝÃD¬O©Ò¿×"§R°£¯S©wÄd¦ì"¥i¥H¬O«ü©w«O¯d(section) (SC) (LANDUSE) (PUBNO) (OPTION) (METHOD) (MUPLAN) (DUPLAN) (ORG_FID)¼ÐÃDªº¤@¾ã­ÓÄæ¦ì¡A¨ä¥LµL½×¬Æ»ò¼ÐÃDªºÄæ¦ì³£§R±¼ªº¼gªk¶Ü(¥Î¥H½T«O§å¦¸®É¬Y¤@­ÓÀɪº¨Ò¥~¦h¤@±øµL¶·«O¯dªºÄd¦ì)

stillfish00

¦^´_ 5# billchenfantasy
¦P¤@¬¡­¶Ã¯¬O¤£¯à¦³¦h­Ó¬Û¦P¦WºÙªº¤u§@ªí

§A­nªº§å¦¸³B²z¬O³o¼Ë¶Ü? (¥i¿ï¦h­ÓÀÉ®×)

1. Private Sub CommandButton1_Click()
   
2. Dim Source, f
   
3. 
   
4. '¥i¿ï¾Ü¦h­ÓÀÉ®×
   
5. Source = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", _
   
6.                                     MultiSelect:=True)
   
7. If TypeName(Source) = "Boolean" Then If Source = False Then Exit Sub
   
8. 
   
9. For Each f In Source
   
10. '¶}±ÒÀÉ®×/¬¡­¶Ã¯
    
11. With Workbooks.Open(f)
    
12.     '¹ï©Ò¦³¤u§@ªí
    
13.     For i = 1 To ActiveWorkbook.Sheets.Count
    
14.         '½Æ»s¤u§@ªí¨ì¥»¬¡­¶Ã¯
    
15.         .Sheets(i).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
16.         '¥»¬¡­¶Ã¯¤¤¸Ó¤u§@ªí
    
17.         With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
18.            '¨ÌE,FÄæ±Æ§Ç
    
19.            .[A1].CurrentRegion.Sort Key1:=.[E:E], Order1:=xlAscending, _
    
20.                                     Key2:=.[F:F], Order2:=xlAscending, _
    
21.                                     Header:=xlYes
    
22.            '§R°£¤£­nªºÄæ
    
23.            .Range("A:C,E:I,K:L,Q:Q").Delete Shift:=xlToLeft
    
24.         End With
    
25.     Next i
    
26.     'Ãö³¬ÀÉ®×
    
27.     .Close
    
28. End With
    
29. Next f
    
30. End Sub

½Æ»s¥N½X

billchenfantasy

@@Private Sub CommandButton2_Click()
ªºOK¤F¹ï©ó±zªº¸Ñµª·PÁ¸U¤À¡A§Ú¨Ó¦n¦n¬ã¨s¤@¤U
Private Sub CommandButton1_Click()ªº·N«ä¬O»¡
"½Æ»s¤u§@ªí¹L¨Ó®É¦W¦rÀ³¸Ó¬O©M­ì¥»¤u§@ªí¤@¼Ëªº"¨S¿ù
¦]¬°§ÚªºÀɮצ³«Ü¦h¡A¦Ó¥BÁöµM¤º®e®æ¦¡¤@¼Ë¦ýÀɦW¤£¤@¼Ë¡A§Úªº·Qªk¬O­n«ç»ò¼Ë
§â¤£¦PÀɦWªºÀɮ׶׶i¨Ó¤§«á³£¥i¥H°õ¦æPrivate Sub CommandButton2_Click()¡A
¦]¬°«ü©wªºWith Sheets("¤u§@ªí¦WºÙ")¤u§@ªí¦WºÙ·|ÅÜ
µ¥³o­Ó³£¤F¸Ñ¤F±µ¤U¨Ó¦A·Q¤U¤@¨B«ç»ò§å¦¸
¦A¦¸ÁÂÁ§A¼Ú