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

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

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

½Ð°Ý¦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)
·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

  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.         End With
  22.     Next
  23.     GoTo 100
  24. ERR: MsgBox "ERROR"
  25. 100:     Application.EnableEvents = True
  26. End Sub
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2016-5-23 05:57 ½s¿è

¦^´_ 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¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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

TOP

¦^´_ 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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  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ªº«ü¾É!!!

TOP

        ÀR«ä¦Û¦b : ®É®É¦n¤ß´N¬O®É®É¦n¤é¡C
ªð¦^¦Cªí ¤W¤@¥DÃD