[µo°Ý] ½Ð°Ý¤½¦¡ ActiveCell.FormulaR1C1 ¥N¸¹¦p¦ó¼g

cji3cj6xu6

½Ð°Ý¥H¤U¤½¦¡ ªºR[-1703]¥N¸¹¦p¦ó¼g
ActiveCell.FormulaR1C1 = "=sheet4!R[-1703]C[7]"

¦]¬°-1703 ¬O»¼¼W¡A½Ð°Ý¸Ó¦p¦ó¥H¥N¸¹®Ñ¼g¡C

Thanks.

GBKEE

¦^´_ 1# cji3cj6xu6
¸Õ¸Õ¬Ý

1. Sub Ex()
   
2.    Dim i As Long
   
3.    i = -100
   
4.    ActiveCell.FormulaR1C1 = "=sheet1!R[" & i & "]C[7]"
   
5. End Sub

½Æ»s¥N½X

cji3cj6xu6

·d©w¡AÁÂÁÂGBKEE¤j¡ã

cji3cj6xu6

¦A½Ð±ÐGBKEE¤j¡A¶W³sµ²¦ì§}ªº¼gªk
¥H¤U¬O2010¿ý»sªº¼gªk
     Range("S2").Select
    Selection.Hyperlinks(1).SubAddress = "sheet1!A1700"
    Range("S3").Select
    Selection.Hyperlinks(1).SubAddress = "'sheet3'!A1700"

¥ý«e§Ú¦bexcel2003®É¼gªk¬O¡G
'Sheet3.Hyperlinks.Add Selection, Address:="", SubAddress:="'" & Range("a" & DQ).Value & "'" & "!A1675" ' ³]©w¶W³sµ²¦ì§}

¨ä¤¤sheet3 ¬O¶W³sµ²¦ì§}Àx¦sªº­¶­±¡A
Range("a" & DQ) ¬O§Ú¦s©ñSHEET¦WºÙªº¦a¤è

½Ð°Ý§Ú¸Ó¦p¦ó­×§ï¡C

GBKEE

¦^´_ 4# cji3cj6xu6
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3. Dim DQ
   
4. DQ = 5
   
5. Sheet3.Hyperlinks.Add Selection, Address:="", SubAddress:=Range("a" & DQ).Value & "!A1675"  ' ³]©w¶W³sµ²¦ì§}
   
6. 'Selection ÄÝ©Ê"   ¦pªG¬O Application ª«¥ó¡A«h¶Ç¦^²{¥Îµøµ¡¤¤ªº¿ï©wª«¥ó¡A¦pªG¬O Windows ª«¥ó¡A«h¶Ç¦^«ü©wµøµ¡¡C
   
7. 
   
8. '©Ò¥H¤W¦¡°õ¦æ«á·|¬OActiveSheet(§@¥Î¤¤ªº¤u§@ªí)ªº§@¥Î¤¤ªºÀx¦s®æ:·s¼W¤@¶W³sµ² (¤£¬O¦bSheet3)
   
9. 
   
10. 'SubAddress:=Range("a" & DQ).Value (µ{¦¡½X¬O¦b¤u§@ªí¼Ò²Õ:¬O³o¤u§@ªíª«¥óªº Range("a" & DQ)
    
11.                                   '(µ{¦¡½X¬O¦b¤@¯ë¼Ò²Õ:¬OActiveSheet(§@¥Î¤¤ªº¤u§@ªí)ªº Range("a" & DQ)
    
12. 
    
13. '*************************­×¥¿¦p¤U *****************************************************************
    
14. 
    
15. With Sheet3  '¦b .Range("a10") ·s¼W¤@¶W³sµ²
    
16.     .Hyperlinks.Add .Range("a10"), Address:="", SubAddress:=ActiveSheet.Range("a1").Value & "!A1675"
    
17.     ' ³]©w¶W³sµ²¦ì§}:³sµ²¨ì§@¥Î¤¤¤u§@ªí.Range("a1").Valueªº¤u§@ªí¦WºÙ
    
18.                     '©Î¬O«ü©w¤u§@ªí SubAddress:=SHEETS("SHEET2").Range("a1").Value
    
19. End With
    
20. '¦P¤W Sheet3.Hyperlinks.Add Sheet3.Range("a10"), Address:="", SubAddress:=ActiveSheet.Range("a1").Value & "!A1675"
    
21. 
    
22. End Sub

½Æ»s¥N½X

cji3cj6xu6

GBKEE¤j¡A
ÁÂÁÂÅo¡A§Ú¦A¸Õ¬Ý¬Ý¡ã

cji3cj6xu6

½Ð°Ý¥H¤U¤½¦¡ ªºsheet4 ­Y¬O¦³§ó°Ê¡A¨Ò¦p±qsheets(1), sheets(2), sheets(3)......, sheets(10) »yªk­n¦p¦ó¼g
ActiveCell.FormulaR1C1 = "=sheet4!R[-1703]C[7]"

ÁÂÁ¡ã

GBKEE

¦^´_ 7# cji3cj6xu6

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim i As Integer
   
4.     For i = 1 To 10
   
5.         ActiveCell.Offset(i - 1).FormulaR1C1 = "=" & Sheets(i).Name & "!R[-1703]C[7]"
   
6.     Next
   
7. End Sub

½Æ»s¥N½X

cji3cj6xu6

­ì¨Ó¥i¥H³o¼Ë¼g¡A§Úª½±µ±Nsheet name §ì¥X¨Ó¡AµM«á±a¤J¡A
¦ýG¤jªº¼gªk¤ñ¸ûÀu¡AÁÂÁ¡ã

cji3cj6xu6

½Ð°Ý  Selection.Hyperlinks(1).SubAddress = "'sheet3'!A1700"
¸Ì­±ªº1700·|¬O­ÓÅܼơA°²³]§Ú©ñ¦b sheets(25).Range("a40")¡A
½Ð°Ý­n¦p¦ó±Nsheets(25).Range("a40")±a¶i¥h¡HÁÂÁ¡ã