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

jakcy1234

¤U¸ü (237.06 KB)

2013-9-5 14:22

¦³´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. 
    
11. End Sub

½Æ»s¥N½X

GBKEE

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

jakcy1234

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

GBKEE

¦^´_ 4# jakcy1234
http://forum.twbts.com/thread-10339-1-1.html  ªº 13©«

jakcy1234

¦^´_ 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³ò

GBKEE

¦^´_ 5# jakcy1234
¤£¿ù,±ø±ø¹D¸ô³qù°¨.