Fill Link to a range according to cell value

[HaroonSid]

hi,
i am using below code to fill external link to to my range and the name of the sheets are from cell's value

Sub HaroonSidxxxx()
Dim i As Long, y
ReDim y(4 To Range("D" & Rows.Count).End(3).Row)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = LBound(y) To UBound(y)
Let Range("E" & i & ":F" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!D$1,"""")"
Let Range("H" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!G$1,"""")"
Let Range("J" & i & ":K" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!D$2,"""")"
Let Range("M" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!G$2,"""")"

Let Range("O" & i & ":P" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!D$3,"""")"
Let Range("R" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!G$3,"""")"

Let Range("T" & i & ":U" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!D$4,"""")"
Let Range("W" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!G$4,"""")"


Let Range("Y" & i & ":Z" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!D$5,"""")"
Let Range("AB" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!G$5,"""")"

Let Range("AD" & i & ":AE" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!D$6,"""")"
Let Range("AG" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!G$6,"""")"

Let Range("AI" & i & ":AJ" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!D$7,"""")"
Let Range("AL" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & ".8.17.xlsb]SUMALL'!G$7,"""")"
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

and i need to change in this part of the code

ReDim y(4 To Range("D" & Rows.Count).End(3).Row)

because of above the code fill data always from row 4 to last none blank row of column D
i want if data is already filled till row 10 then this code fill data from row 10 instead of row4, if data is filled till 15 then code fill data from row 16 not from 4,

thank you
hope some one will work

[sintek]

................deleted...............

[HaroonSid]

Found the accurate solution
thanks to me

Dim LastLine As Long
Dim i As Long, y
LastLine = Range("E34:E" & Rows.Count).End(xlUp).Row + 1

'ReDim y(4 To Range("D34:D" & Rows.Count).End(3).Row) 'START FROM ROW 4
On Error Resume Next
ReDim y(LastLine To Range("D34:D" & Rows.Count).End(3).Row) ' START FROM LAST BLANK ROW
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = LBound(y) To UBound(y)
Let Range("E" & i & ":F" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!D$1,"""")"
Let Range("H" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!G$1,"""")"
Let Range("J" & i & ":K" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!D$2,"""")"
Let Range("M" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!G$2,"""")"

Let Range("O" & i & ":P" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!D$3,"""")"
Let Range("R" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!G$3,"""")"

Let Range("T" & i & ":U" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!D$4,"""")"
Let Range("W" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!G$4,"""")"


Let Range("Y" & i & ":Z" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!D$5,"""")"
Let Range("AB" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!G$5,"""")"

Let Range("AD" & i & ":AE" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!D$6,"""")"
Let Range("AG" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!G$6,"""")"

Let Range("AI" & i & ":AJ" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!D$7,"""")"
Let Range("AL" & i & "").Value = "=IFERROR('Z:\42766\Jan 2 Dec 2014\1.12.16\[" & Cells(i, "D").Value & "." & Range("A2").Value & "." & Range("A1").Value & ".xlsb]SUMALL'!G$7,"""")"
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True

[HaroonSid]

@sintek
i just checked your msg which one you deleted,
its the same same as i used

Dim nRow As Long
nRow = Range("D" & Rows.Count).End(xlUp).Row + 1
ReDim y(nRow To Range("D" & Rows.Count).End(xlUp).Row + 1)

but why you deleted this ???

[sintek]

Hi HaroonSid

I got caught up in a clients code that I completely forgot what I wanted to do and was not sure of your starting row....

[HaroonSid]

ok
thank you for reply