What is the .Value of aryW(i - 6) ?
Just a file name ?

Also for the array, you can make it much simpler:
Dim aryW()
Dim arry As Variant
Dim i As Long

arry = Range("C4:C63").Value
'....
,Note ath added "1" in the array index
Range("C" & i).Formula = "=[" & aryW(i - 6,1) & ".xls]LeakFreq!$B$6"

NickHK

"ccl28" wrote in
message ...

Hi,

Anyone can help to auto detect the path of the file in
"=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6" ?

The user will key in the name of the file in C4 but not the path.

I have to click 6 times to locate the path. It is very troublesome.

Any suggestion? or ask the user to key in the path for 1 times?

Thank you. Attached please find the code.



Sub Macro1()
Application.ScreenUpdating = False
Dim i As Long
Dim aryW(1 To 60)
Dim intNoFiles As Integer

intNoFiles = Sheet1.Range("C4").CurrentRegion.Rows.Count - 1

answer = MsgBox("Before you start:" & Chr$(13) _
& "Please make sure the Parts Count File Name is filled in cell
C4." _
& Chr$(13) & Chr$(13) & "Do you want to continue?", vbYesNo,
"")
If answer = vbNo Then
End
End If
'Count how many rows in the block by C4


Windows("SummaryLeakFreq").Activate
Sheet1.Select
aryW(1) = Range("C4").Value
aryW(2) = Range("C5").Value
aryW(3) = Range("C6").Value
aryW(4) = Range("C7").Value
aryW(5) = Range("C8").Value
aryW(6) = Range("C9").Value
aryW(7) = Range("C10").Value
aryW(8) = Range("C11").Value
aryW(9) = Range("C12").Value
aryW(10) = Range("C13").Value
aryW(11) = Range("C14").Value
aryW(12) = Range("C15").Value
aryW(13) = Range("C16").Value
aryW(14) = Range("C17").Value
aryW(15) = Range("C18").Value
aryW(16) = Range("C19").Value
aryW(17) = Range("C20").Value
aryW(18) = Range("C21").Value
aryW(19) = Range("C22").Value
aryW(20) = Range("C23").Value
aryW(21) = Range("C24").Value
aryW(22) = Range("C25").Value
aryW(23) = Range("C26").Value
aryW(24) = Range("C27").Value
aryW(25) = Range("C28").Value
aryW(26) = Range("C29").Value
aryW(27) = Range("C30").Value
aryW(28) = Range("C31").Value
aryW(29) = Range("C32").Value
aryW(30) = Range("C33").Value
aryW(31) = Range("C34").Value
aryW(32) = Range("C35").Value
aryW(33) = Range("C36").Value
aryW(34) = Range("C37").Value
aryW(35) = Range("C38").Value
aryW(36) = Range("C39").Value
aryW(37) = Range("C40").Value
aryW(38) = Range("C41").Value
aryW(39) = Range("C42").Value
aryW(40) = Range("C43").Value
aryW(41) = Range("C44").Value
aryW(42) = Range("C45").Value
aryW(44) = Range("C46").Value
aryW(44) = Range("C47").Value
aryW(45) = Range("C48").Value
aryW(46) = Range("C49").Value
aryW(47) = Range("C50").Value
aryW(48) = Range("C51").Value
aryW(49) = Range("C52").Value
aryW(50) = Range("C53").Value
aryW(51) = Range("C54").Value
aryW(52) = Range("C55").Value
aryW(53) = Range("C56").Value
aryW(54) = Range("C57").Value
aryW(55) = Range("C58").Value
aryW(56) = Range("C59").Value
aryW(57) = Range("C60").Value
aryW(58) = Range("C61").Value
aryW(59) = Range("C62").Value
aryW(60) = Range("C63").Value


With Sheets("LeakFrequencySummary")
For j = 1 To intNoFiles
For i = 7 To j + 6

'Failure Cases
Range("C" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$B$6"
'Freq
Range("D" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$D$39"
'Pin
Range("E" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$E$39"
'Small
Range("F" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$F$39"
'Medium
Range("G" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$G$39"
'Large
Range("H" & i).Formula = "=[" & aryW(i - 6) & ".xls]LeakFreq!$H$39"
Next i
Next j

End With
Application.ScreenUpdating = True
End Sub


--
ccl28
------------------------------------------------------------------------
ccl28's Profile:
http://www.excelforum.com/member.php...o&userid=36095
View this thread: http://www.excelforum.com/showthread...hreadid=570643