[µo°Ý] ½Ð°Ý¦p¦ó¹ïÀ³¤u§@ªíªº¦WºÙ

4016

½Ð°Ý¦U¦ì«e½ú¡A¤p§Ì»Ý­n°µ¤@­Ó¸ê®Æ²Î­pªº³øªí¡A§@¤F¥H¤U¨ç¼Æ
°ÝÃD¦b©ó¤u§@ªíªº¦WºÙ¡A¥Ñ©ó»Ý­n¶}10´X­Ó¤u§@ªí¡A¥B¦WºÙ³£·|¦A¥t¥~©R¦W
³y¦¨®ÄªG³£¶È¶]¨ì«~ºØ¦W(1)³o­Ó¤u§@ªí¸Ì¡A
¤£ª¾¹D¦p¦ó¯à¹ïÀ³¨ì¤u§@ªíªº¦WºÙ¡AÅý§Ú10´X­Ó¤u§@ªí³æ­×§ï¦U¦Û¦WºÙ¡A¤]³£¯à¶¶§Q°õ¦æ...ÁÂÁ Help²Î­p³øªí.rar (17.3 KB)

¤U¸ü¦¸¼Æ: 6

2016-5-21 12:35

·P¿E¤£ºÉ!!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
RR = Target.Row
CC = Target.Column
If RR = 3 And CC <= 4 Then
LR = Range("B3").End(xlDown).Row
    ActiveWorkbook.Worksheets("«~ºØ¦W(1)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("«~ºØ¦W(1)").Sort.SortFields.Add Key:=Target.Resize(LR, 1), _
         SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("«~ºØ¦W(1)").Sort
        .SetRange Range("A3:D" & LR)
                .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End If

End Sub

ML089

1. ' ©ñ¦b ThisWorkbook¡A
   
2. ' ¨ä¥L¤u§@ªíªº Private Sub Worksheet_SelectionChange(ByVal Target As Range) ½ÐÃö³¬©Î§R°£
   
3. '
   
4. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   
5.     Dim xDat As Range, xKey As Range, LR
   
6.    
   
7.     '    Debug.Print Sh.Name
   
8.     '    Debug.Print Target.Address
   
9.     '
   
10.     If Application.Intersect([A3:D3], Target) Is Nothing Then Exit Sub
    
11.     Application.EnableEvents = False
    
12.     On Error GoTo ERR
    
13.     Debug.Print "Sort ..." & Target.Address
    
14.     For Each xSh In Sheets
    
15.         ' Debug.Print xSh.Name
    
16.         With xSh
    
17.             LR = .Range("B3").End(xlDown).Row
    
18.             Set xDat = .Range("A3:D" & LR)
    
19.             Set xKey = .Range(Target.Address).Resize(LR - 2)
    
20.             xDat.Sort key1:=xKey, order1:=xlAscending, Header:=xlYes
    
21. 
    
22.         End With
    
23.     Next
    
24.     GoTo 100
    
25. ERR: MsgBox "ERROR"
    
26. 100:     Application.EnableEvents = True
    
27. End Sub

½Æ»s¥N½X

4016

¦^´_ 2# ML089


ML089¤j ±z¦n

½Ð°Ý¤@¤UÃö©ó§A©Ò«ü¾Éªº³¡¤À
    Debug.Print "Sort ..." & Target.Address
³o¦æ¦b§A³Ì¤U­±¦³»¡©ú¬O­n
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

§Ú¥hgoogle¬OÃö©ó°}¦C¤½¦¡ªº¨Ï¥Î..
¥i¬O§Ú¯uªº¤£¤Ó©ú¥Õ­n¦b­þ­Ó¦a¤è«ö  CTRL+SHIFT+ENTER ¿é¤J
¤~¯à¥¿±`¨Ï¥Î¡A¯à§_¦b«üÂI¤@¤U¡AÁÂÁÂ...

ML089

¦^´_ 3# 4016

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

³o¬O«ü§Ú¼g¤½¦¡®É­Y¬O°}¦C¤½¦¡®É·|¥H¦¹¤À¬°¤@¯ë¤½¦¡©Î°}¦C¤½¦¡ªº»¡©ú
¦C¦p
{=SUM(IF(a4:a43<10,a4:a43*2))}
ªí¥Ü¦¹¤½¦¡¬°°}¦C¤½¦¡
1. ±N¤½¦¡¿é¤J(©Î½Æ»s¶K¤W)½s¿è¦C¡A=SUM(IF(a4:a43<10,a4:a43*2))
2. ¤£¥Îenter¿é¤J¡A­n§ï¥ÎCTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡¡A¿é¤J¦¨¥\«á¤½¦¡¥~´N·|¼W¥[ {...}
3. CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡§Þ¥©¬O¥ý«ö¦íCTRL+SHIFT¤£©ñ¦b«ö ENTER´N¯à¤TÁä¿é¤J¤½¦¡

4016

¦^´_  4016

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

³o¬O«ü§Ú¼g¤½¦¡®É­Y¬O°}¦C¤½¦¡®É·|¥H¦¹ ...
ML089 µoªí©ó 2016-5-23 05:56

©Ò¥H¸ò§Ú³o­Ó°ÝÃD¨SÃö«Yªº·N«ä¶Ü??
¦]¬°§Ú±NML089 ¤j©Ò­×§ï«áªº¤åªk
' ©ñ¦b ThisWorkbook¡A
' ¨ä¥L¤u§@ªíªº Private Sub Worksheet_SelectionChange(ByVal Target As Range) ½ÐÃö³¬©Î§R°£
¶K¤W«á¡A¨ä­ì¥»±Æ§Çªº®ÄªG ¨ÃµLªk°õ¦æ..??
³o°ÝÃD¬O¥X¦b­þ¸Ì©O?
³Â·Ð¯à¦A«üÂI¤@¤U¶Ü
ÁÂÁÂ

ML089

¦^´_ 5# 4016

¥u¯àÂI¿ï A3:D3³o4Àx¦s®æ¤~¯à¦³§@¥Î
§A¥i¥H­×§ï¤U¦C¤¤ªº [A3:D3]§@¬°ÂI¿ï°õ¦æªº¦ì¸m

If Application.Intersect([A3:D3], Target) Is Nothing Then Exit Sub

4016

¦^´_  4016

¥u¯àÂI¿ï A33³o4Àx¦s®æ¤~¯à¦³§@¥Î
§A¥i¥H­×§ï¤U¦C¤¤ªº [A33]§@¬°ÂI¿ï°õ¦æªº¦ì¸m

If ...
ML089 µoªí©ó 2016-5-23 22:29

¦³¡A§Ú¸Õ¥X¨Ó¤F!!!
«D±`ÁÂÁÂML089¤jªº«ü¾É!!!