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

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

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

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

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

Thanks.

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¦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

TOP

¦^´_ 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. '©Ò¥H¤W¦¡°õ¦æ«á·|¬OActiveSheet(§@¥Î¤¤ªº¤u§@ªí)ªº§@¥Î¤¤ªºÀx¦s®æ:·s¼W¤@¶W³sµ² (¤£¬O¦bSheet3)

  8. 'SubAddress:=Range("a" & DQ).Value (µ{¦¡½X¬O¦b¤u§@ªí¼Ò²Õ:¬O³o¤u§@ªíª«¥óªº Range("a" & DQ)
  9.                                   '(µ{¦¡½X¬O¦b¤@¯ë¼Ò²Õ:¬OActiveSheet(§@¥Î¤¤ªº¤u§@ªí)ªº Range("a" & DQ)

  10. '*************************­×¥¿¦p¤U *****************************************************************

  11. With Sheet3  '¦b .Range("a10") ·s¼W¤@¶W³sµ²
  12.     .Hyperlinks.Add .Range("a10"), Address:="", SubAddress:=ActiveSheet.Range("a1").Value & "!A1675"
  13.     ' ³]©w¶W³sµ²¦ì§}:³sµ²¨ì§@¥Î¤¤¤u§@ªí.Range("a1").Valueªº¤u§@ªí¦WºÙ
  14.                     '©Î¬O«ü©w¤u§@ªí SubAddress:=SHEETS("SHEET2").Range("a1").Value
  15. End With
  16. '¦P¤W Sheet3.Hyperlinks.Add Sheet3.Range("a10"), Address:="", SubAddress:=ActiveSheet.Range("a1").Value & "!A1675"

  17. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

½Ð°Ý¥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]"

ÁÂÁ¡ã

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

½Ð°Ý  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ÁÂÁ¡ã

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD