Application-defined or object defined error

[JeffGrant]

Hi All,

I am getting a "Runtime 1004 Error, Application-defined or object defined error" on this line.....

.Range("D2:D" & i).Formula2 = "=INDEX(Data!$A$2:$A$250,(ROWS(Data!$1:1)-1)*8+COLUMNS(Data!A:$A))"

If I comment this line out, the error just moves to the next line which is very similar to this one.

I am stuck because i cant see where the error is. Any guidance is much apprecaited.

Noting that Sheet1 has the name "Data"


Here is an extract of the code.

ThisWorkbook.Unprotect 1234
Sheet4.Select
With ActiveSheet
     .Unprotect 1234
 
    'Find Last Row on Sheet4
     irow = .Range("A" & Rows.Count).End(xlUp).Row + 1

    'Write formulas into Sheet 4, pulling information from Sheet 1
     If Sheet1.Range("A9").Value <> "EW" Then
         .Range("D2:D" & i).Formula2 = "=INDEX(Data!$A$2:$A$250,(ROWS(Data!$1:1)-1)*8+COLUMNS(Data!A:$A))"
         .Range("E2:E" & i).Formula2 = "=VALUE(MID(INDEX(Data!$A$2:$A$250,(ROWS(Data!$1:1)-1)*8+COLUMNS(Data!A:$B)),4,4))"
         .Range("F2:F" & i).Formula2 = "=INDEX(Data!$A$2:$A$250,(ROWS(Data!$1:1)-1)*8+COLUMNS(Data!A:$C))"
     End If

    .Protect 1234
End With

Thanks for your help

PS...if I simply paste the formulas directly into the cell, the formulas work fine.
PPS. I also put a simple test like this in and it worked fine too. =IF(Data!A9<> "EW",INDEX(Data!$A$2:$A$250,(ROWS(Data!$1:1)-1)*8+COLUMNS(Data!A:$A)),"No")

 

[Alex Blakenburg]

Shouldn’t “i” be “irow” ?

 

[JeffGrant]

Oh my, How embarassing is that. If you could see my face now,it would be so red.....What a rooky...

 

[JeffGrant]

Thanks Alex.

 

[Alex Blakenburg]

Haha. No problem, we all do it.