½Ð°Ý¸Ó¦p¦ó¨Ï¥Î±Æ§Ç¨Ã±N»Ýn¸ê®Æ±a¥X¦Üsheet2
- ©«¤l
- 45
- ¥DÃD
- 19
- ºëµØ
- 0
- ¿n¤À
- 84
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows XP
- ³nÅ骩¥»
- Office 2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- Taiwan
- µù¥U®É¶¡
- 2010-8-9
- ³Ì«áµn¿ý
- 2015-4-14
|
½Ð°Ý¸Ó¦p¦ó¨Ï¥Î±Æ§Ç¨Ã±N»Ýn¸ê®Æ±a¥X¦Üsheet2
½Ð°Ý¸Ó¦p¦ó¨Ï¥Î±Æ§Ç¨Ã±N»Ýn¸ê®Æ±a¥X¦Üsheet2
¨Ò¦p¡GD07-01
D07-02
D07-03
D08-01
D08-02
D08-03 |
|
|
|
|
|
|
- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-6-8 16:44 ½s¿è
¦^´_ 1# yueh0720 - Option Explicit
- Sub Ex()
- Dim Ar(), xl As Integer
- With Sheets(1) '²Ä1Ó¤u§@ªí
- .AutoFilterMode = False '¨ú®ø ³o¤u§@ªíªº¦Û°Ê¿z¿ï
- Ar = .Range("A1").CurrentRegion.Value '¸ê®ÆÂà¤J°}¦C
- .Range("A1").CurrentRegion.Sort Key1:=.Range("H2"), Order1:=xlAscending, Key2:=.Range( _
- "A2"), Order2:=xlAscending, Header:=xlYes '±Æ§Ç
- .Range("IV:IV") = "" '²M°£IVÄæ¸ê®Æ
- .Columns("H:H").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("IV1"), CriteriaRange:=.Range("IU1:IU2"), Unique:=True
- 'AdvancedFilter ¶i¶¥¿z¿ï: HÄ椣«½Æ¸ê®Æ ¿z¿ï¨ì.Range("IV1")
- xl = 2 '±q ²Ä2¦C ¶}©l
- Do While .Range("IV" & xl) <> "" '±ø¥ó¦¨¥ß: °õ¦æ°j°é
- If Sheets.Count < xl Then Sheets.Add , Sheets(Sheets.Count) '¤u§@ªí¼Æ¤p©óxl:·s¼W¤u§@ªí
- .Range("A1").AutoFilter Field:=8, Criteria1:=.Range("IV" & xl) '¦Û°Ê¿z¿ï: ²Ä8Äæ=.Range("IV" & xl)
- .Range("A1").CurrentRegion.SpecialCells(xlCellTypeConstants).Copy Sheets(xl).[A1] '¿z¿ï¨ìªº¸ê®Æ:½Æ»s¨ì «ü©w¤u§@ªíªº[A1]
- xl = xl + 1 '±q²Ä2¦C: ©¹¤U¤@¦C
- Loop
- .AutoFilterMode = False
- .Range("A1").CurrentRegion.Value = Ar '¨ú¥X°}¦C¸ê®Æ ¸m¦^
- End With
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 967
- ¥DÃD
- 0
- ºëµØ
- 0
- ¿n¤À
- 1001
- ÂI¦W
- 0
- §@·~¨t²Î
- WIN XP
- ³nÅ骩¥»
- OFFICE 2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥x¥_
- µù¥U®É¶¡
- 2010-11-29
- ³Ì«áµn¿ý
- 2022-5-17
|
¦^´_ 1# yueh0720 - Sub xx()
- Dim Ar(1 To 1000, 1 To 10)
- Sheets(1).Select
- Br = Array("", "", "Discharge", "charge")
- For Sh = 2 To 3
- Set d = CreateObject("scripting.dictionary")
- [A1].Sort Key1:=[A1], Order1:=xlAscending, Header:=xlGuess
- [A1].AutoFilter Field:=8, Criteria1:=Br(Sh)
- I = 0
- For Each A In Range("A2:A" & [A1].End(xlDown).Row).SpecialCells(xlCellTypeVisible)
- If Not d.exists(A.Value) Then
- I = I + 1: J = 1
- d(A.Value) = A.Offset(0, 1)
- Ar(I, J) = A.Offset(0, 17)
- Else
- J = J + 1
- Ar(I, J) = A.Offset(0, 17)
- End If
- Next
- Sheets(Sh).Cells = ""
- Sheets(Sh).[A1:M1] = Array("Dock-Ch", "Serial No", "Action", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
- Sheets(Sh).[A2].Resize(d.Count, 1) = Application.Transpose(d.keys)
- Sheets(Sh).[B2].Resize(d.Count, 1) = Application.Transpose(d.items)
- Sheets(Sh).[C2].Resize(d.Count, 1) = Br(Sh)
- Sheets(Sh).[D2].Resize(d.Count, 2) = Ar
- Set d = Nothing: Erase Ar
- Next Sh
- Sheets(1).AutoFilterMode = False
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|