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

[µo°Ý] cell double click & copy »y¥y

[µo°Ý] cell double click & copy »y¥y



¦³´X­Ó°ÝÃD·Q°Ý°Ý...............

°ÝÃD (1.)
§Ú·Q°µ¤@­Óbutton click (restart auto filter)
¦ý¬O¨S¦³ shift + space ¬J»y¥y ( ¥u¥i¥H¥Î Rows("1:20").Select )
¦pªGrow ·|¤@ª½¼W¥[ 21 , 22 , 23 ............N ¦æ,
¦Ó¤S¤£·|SELECT ¼vÅT¨ì 22¦Ü26¦æ (¦p¹Ï)

·Q°Ý¦³¨S¦³¦n¹³  shift + space ¬J»y¥y ¥Î A1 column ¬°¥Ø¼Ð¨ì N¦æ (³Ì§Àªº½s¸¹) µM«á select all row

§Ú¥u·Q¤F¤@­Ó¿ìªk ¦b M20®æ¥[¤F°O¸¹(¦p¹Ï), ´N¥i¥H¿ï¨ì A1~~L20 ³£¬O filter ½d³ò
  1. Sub Macro19()
  2.     Range("A1").Select
  3.     Range(Selection, Selection.End(xlDown)).Select
  4.     Range(Selection, Selection.End(xlToRight)).Select
  5.     Selection.AutoFilter
  6.     Selection.AutoFilter   
  7. End Sub
½Æ»s¥N½X
================================================================================


°ÝÃD (2.)

ÁÙ¦³ reset auto filter, ¬O¤£¬O¥u¥i¥H¤@­Ó¤@­Ó±Æ ( ¥i¥H¤£¥i¥H¤@¦¸¹L 1 ¦Ü 5 ¤@°_ ) ?
¦pªG¿ï¤F«Ü¦hfilter , data ¤S¤Ó¦h, ³v­Ó reset ´N·|«ÜºC.
  1. Sub Macro20()
  2.     Selection.AutoFilter Field:=1
  3.     Selection.AutoFilter Field:=2
  4.     Selection.AutoFilter Field:=3
  5.     Selection.AutoFilter Field:=4
  6.     Selection.AutoFilter Field:=5
  7. End Sub
½Æ»s¥N½X
=================================================================================

°ÝÃD (3.) cell double click & copy ( ¤£¬O­n§Y®Épaste, ¥u¦s¦b°O¾Ð¤¤  ), ½Ð°ÝÀ³¸Ó­nÂI§ï ?
  1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
  2. With Worksheets("Sheet2").UsedRange.Cells
  3. Dim r As Long
  4. r = Target.Row
  5. For Each c In Rows(r).Cells
  6. b = c.Address
  7. Worksheets("Sheet3").Range(b) = c
  8. Next c
  9. End With

  10. End Sub
½Æ»s¥N½X

¦^´_ 1# jakcy1234
2# sunnyso »¡¤W¶ÇÀɮ׬O¤è«K¸Ñµª(¤£¥²½M¤lºN¶H)
  1. Option Explicit
  2. Sub Macro19()
  3.     Dim Rng As Range
  4.     With Sheets("SHEET1")
  5.         Set Rng = .Range(.[A1], .[A1].End(xlDown)).Resize(, 12)            'M20®æ¥[¤F°O¸¹(¦p¹Ï), ´N¥i¥H¿ï¨ì A1~~L20 ³£¬O filter ½d³ò
  6.         'Set Rng = .Range(.[A1], .[A1].End(xlDown)).Resize(, .[L1].Column) 'M20®æ¥[¤F°O¸¹(¦p¹Ï), ´N¥i¥H¿ï¨ì A1~~L20 ³£¬O filter ½d³ò
  7.     End With
  8.     MsgBox Rng.Address
  9. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# GBKEE

ÁÂÁ§Aªº¸Ñµª
§Aªº»y¥y¤ñ§ÚªºÂ²³æ«Ü¦h, ¥H¤W3­Ó°ÝÃD§Ú¬Q¤Ñ¤w¸g¸Ñ¨M¤F.


¥t¥~¨ä¹êcell double click & copy ¤w§ä¨ì¤èªk, ¦ý¬O¦pªG§Ú¦b 1¦Ü8¦æ¤¤¼W¥[N¦æ¼Æ , «ü©wªº Range ½d³ò´N¥¢¥h®Ä¥Î,
¬O¤£¬O­n¥Îvlookup offset ¨Ó³]©w (¦pA9¬° [¶µ¥Ø¦W] >> vlookup ¶µ¥Ø¦W offset Range ¾É¦V 10,0 : 20,3 ),
§Ú¥uÃÑvlookup ¨ç¼Æformula ¦ýmacro ´N¤£À´«ç¼g, ½Ð°Ý­n«ç§ï?
  1. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  2.     If Not Intersect(Range("A10:D20"), Target) Is Nothing Then
  3.         Application.EnableEvents = False
  4.         Range("E20") = Target
  5.         Application.EnableEvents = True
  6.             Range("E20").Select
  7.     Selection.Copy
  8.     End If
  9. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# jakcy1234
http://forum.twbts.com/thread-10339-1-1.html  ªº 13©«
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE
Dim findValue As Range
Set findValue = Range("E1:E100").Find(What:="¶µ¥Ø", After:=Range(" E1 "), LookIn:=xlFormulas)
If Not findValue Is Nothing Then
...........
End If
(findValue.Offset(1, 0).Address)   ' $E$19 ´NÅÜ $E$20


§Ú¨S¦³lookup©Îindex ³Ì«á§Ú²²³æ³æ¥Î find xlFormulas + offset ¥h¿ï¥Î½d³ò

TOP

¦^´_ 5# jakcy1234
¤£¿ù,±ø±ø¹D¸ô³qù°¨.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD