[µo°Ý] ¨D±Ð°Ñ·Ó¥N¤J¤½¦¡?

mhl9mhl9

A1        F:\th\               
A2        2013               
A3        A               
A4        9-Sep               
A5        09.xls               
A6        abcd               
A7        $AB$36               
                       
='F:\th\2013\A\09-Sep\[09.xls]abcd'!$AB$36                       
½Ð°Ý¦p¦ó§âA1:A7ªº°Ñ·Ó¼g¨ì¤½¦¡¨½¥h?
§Ú¬O³o¼Ë¼gªº
="='" &A1&A2&"\"&A3&"\"&TEXT(A4,"dd-mmm")&"\["&A5&"]"&A6&"'!"&A7
±o¨ìªºstring¬O:
='F:\th\2013\A\09-Sep\[09.xls]abcd'!$AB$36
¦ý³o­Ó¤½¦¡´N¬O¤£work,¨D¦U¦ìÀ°¤â,ÁÂÁÂ!

GBKEE

¦^´_ 10# mhl9mhl9

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Ar()
   
4.     Ar = Array("a1", "A2", "A3")
   
5.     Sheet1.[B1] = "=INDIRECT(" & Ar(0) & "&" & Ar(1) & "&" & Ar(2) & ")"
   
6. End Sub

½Æ»s¥N½X

¦p¹Ï

¤U¸ü (44.57 KB)

2013-9-12 12:57

mhl9mhl9

¦^´_ 9# GBKEE


    °Ñ¬Ý 7#,°ÝÃDÁÙ¬O¨S¸Ñ¨M.
§Ú·Q¥i¯à§Úªº­n¨D¬OµLªk¹F¨ìªº,§Ú·Q°Ý¤@°Ý¬O¤£¬O§@¬°worksheetfunction,¤½¦¡ªº¦r¦ê¤¤2­Ó³æ¤Þ¸¹¤§¶¡¤£¯à¦³"ÅܼÆ"
selection="=a1 & b1" ,selection¥i¥H¨ú±o±aÅܼƪº¤½¦¡
selection="='a1 & b1'",selection¨ú±oªº¬Ostring----a1&b1
§Ú§â§Aªºvb¿é¤J§ï¦¨¦p¤U:
S = "='" & "g2" & "Text(j2, ""yyyy"")" & "\" & "a3" & "\" & "Text(j2, ""dd-mmm"")" & "\[" & "Text(j2, ""dd"")" & ".xls]" & "G3" & "'!" & "j3"
selection=s
selection±o¨ì¤@­Ó¿ù»~ªº¤½¦¡
§Ú§â¿ù»~ªº¤½¦¡¦V¤U½Æ»s,¬Ý¨ì¥u¦³"j3"·|ÅÜ,j4,j5,j6....
¥þ¤¤"g2","j2","a3","g3"³£Åã¥Ü¦r¦ê,¤£¯àÅÜ
©Ò¥H§Ú·Q§Úªº­n¨D¥i¯à¬O¹H¤Ï¤°麽ÅÞ¿è,¬O¿ùªº
§Ú°O±o¦pªG¸ô®|¦r¦ê¤¤¨S¦³ªÅ®æ,¨º»ò³æ¤Þ¸¹´N¥i¥H¤£¥Î,§Ú·|¸Õ¤@¸Õ,¤£¥Î¦ý¤Þ¸¹,¯à¤£¯à§âÅܼÆÅã¥Ü¦b¤½¦¡¤¤.
ÁÂÁ§AªºÀ°§U.¬Q¤Ñ¦£¤F¤@¤Ñ¥¼¯à¦pº@,¤£±o·N¦Ó¨D¨ä¦¸,¥u¯àvb¿é¤J¥þ³¡¤½¦¡,°Ñ¼Æ§ïÅÜ,´N¥u¯à­nuser,«öÁä¨D¨ä­È§ïÅÜ.

GBKEE

¦^´_ 7# mhl9mhl9

1. Sub Ex()
   
2.     Dim S As String
   
3.     S = "='" & [g2] & Format([j2], "yyyy") & "\" & [a3] & "\" & Format([j2], "dd-mmm") & "\[" & Format([j2], "dd") & ".xls]" & [G3] & "'!" & [j3]
   
4.     Selection.Formula = S
   
5. End Sub

½Æ»s¥N½X

mhl9mhl9

¦^´_ 2# stillfish00

indirect¤½¦¡,·í09.xls¤å¥ó¥´¶}®É,¥i¥H¨ú±o­È
'=INDIRECT("'" &$A$1&$A$2&"\"&$A$3&"\"&TEXT($A$4,"dd-mmm")&"\["&$A$5&"]"&$A$6&"'!"&$A$7)

indirect¤½¦¡,·í09.xls¤å¥óÃö³¬®É,#REF!

®³¨«indirect,¥Î¥H¤U¤½¦¡¿é¤J
'="='" &$A$1&$A$2&"\"&$A$3&"\"&TEXT($A$4,"dd-mmm")&"\["&$A$5&"]"&$A$6&"'!"&$A$7
Àx¦s®æÅã¥Üªº¬O,¦ý¤£¯à¨ú±o­È
'='F:\th\2013\A\09-Sep\[09.xls]Sheet1'!AB36

¥Î¤â¤u¿é¤J¥H¤W¤½¦¡,¥i¥H¥¿±`¨ú±o­È
'='F:\th\2013\A\09-Sep\[09.xls]Sheet1'!AB36

¥Î¤½¦¡¨ú±oªº¦r¦ê©M¤â¤u¿é¤Jªº¦r¦ê¬O¤@¼Ò¤@¼Ë,¦ý´N¬O¥Î¤½¦¡¿é¤Jªºnot work,¤â¤u¿é¤J´Nok,§Æ±æ§A¯àÀ°¦£«üÂI°g®|,ÁÂÁÂ

mhl9mhl9

¦^´_ 5# Hsieh

¥Îvb¿é¤J¤½¦¡¬OOKªº,
  Selection.Formula = "='" & [$g$2] & Format([$j$2], "yyyy") & "\" & [$a3] & "\" & Format([$j$2], "dd-mmm") & "\[" & Format([$j$2], "dd") & ".xls]" & [G3] & "'!" & [$j3]
'¥H¤W¿é¤J¥i¥H§ì¨ú­È,¦ýÀx¦s®æ¨½¯d¤Uªº¬O"­È",¤£¬O¤½¦¡,§Ú¤£¯à½Æ»s¤½¦¡!
¬O¤£¬O¥i¥H§ï¤@§ï¤W­±ªº¿é¤J,¥i¥H¿é¤J¤½¦¡,¦Ó¤£¬O¿é¤J­È,´NOK¤F,§Ú§ï¤F100¦¸,³£¥¢±Ñ

mhl9mhl9

¦^´_ 5# Hsieh


    §Y¨Ï§Ú¥´¶}¥~³¡¤å¥óÁÙ¬O¥X"#Ref!"
indirect§Ú¬O¤£¯à¥Î,¦]¬°§Ú¤£¥i¯à·|¥´¶}¥~³¡¤å¥ó,¦ý¬O§Ú¿é¤Jªº¦r¦ê§¹¥þ©MrecorderÅã¥Ü¤@¼Ò¤@¼Ë,¦ý´N¬Onot work
¥i¯à§Ú¨S§â°ÝÃDÁ¿²M·¡,¬O³o¼Ëªº,§Ú­n¥Î¤½¦¡§ì¨ú F:\th\2013\A\09-Sep\09.xls-----sheet1.range("ab36")ªº­È
¨ä¤¤ F:\th\,2013,A,09-Sep,09.xls,sheet1,ab36 ³o7­Ódata¤À§O¦b A1:A7,¼g¤F¦h¦¸³£¥¢±Ñ,½Ð¦U¦ìÀ°¦£«üÂI,¦hÁ¤F

Hsieh

¦^´_ 4# mhl9mhl9

§A±N¨Ó·½Àɮ׶}±ÒÀ³¸Ó´N¯àÅã¥Ü
INDIRECT¨ç¼Æ¥²¶·¦b¥~³¡°Ñ·ÓÀɮצP®É¶}±Ò¤~¯à¥¿½T¨ú±o

mhl9mhl9

¦^´_ 2# stillfish00


    ªð¦^string¬O¥¿½Tªº¦ý¥X¤F"Ref!",¤£¯à¨ú±o F:\th\A\09-Sep\09.xls¨½Range("ab36")ªº­È?

mhl9mhl9

¦^´_ 2# stillfish00

let me try,thanks