Runtime error 1004 Plz HELP!!!

I am getting Runtime error 1004 in the line "ActiveCell.FormulaR1C1 = ...."
for the below mentioned code

Sub Macro17()
'
' Macro17 Macro
'

'
Windows("Test.xlsx").Activate
Sheets("Sheet7").Select

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _

"=IF(AND(ISNA(VLOOKUP(R1C3+1,Sheet6!R1:R1048576,1,0)),ISNA(VLOOKUP(R1C3+2,Sheet6!R1:R1048576,1,0)),ISNA(VLOOKUP(R1C3+3,Sheet6!R1:R1048576,1,0))),"""",(IF(AND(R1C3<>0,(ISNA(VLOOKUP(R1C3+1,Sheet6!R1:R1048576,1,0)))),IF(AND(R1C3<>0,(ISNA(VLOOKUP(R1C3+2,Sheet6!R1:R1048576,1,0)))),
IF(AND(R1C3<>0,(ISNA(VLOOKUP(R1C3+3,Sheet6!R1:R1048576,1,0)))),””,VLOOKUP(R1C3+3,Sheet6!R1:"
& _

",1,0)),VLOOKUP(R1C3+2,Sheet6!R1:R1048576,1,0)),VLOOKUP(R1C3+1,Sheet6!R1:R1048576,1,0))))"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=
_
xlNone, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(-7, 2).Range("A1").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Plz HELP!!!

Nick's picture

it's unclear what you are

it's unclear what you are trying to do, but you can get the code working (i.e. not erroring) by changing to this:

 

MISSING_VARIABLE = 1048576

ActiveCell.FormulaR1C1 = "=IF(AND(ISNA(VLOOKUP(R1C3+1,Sheet6!R1:R1048576,1,0)),ISNA(VLOOKUP(R1C3+2,Sheet6!R1:R1048576,1,0)),ISNA(VLOOKUP(R1C3+3,Sheet6!R1:R1048576,1,0))),"""",(IF(AND(R1C3<>0,(ISNA(VLOOKUP(R1C3+1,Sheet6!R1:R1048576,1,0)))),IF(AND(R1C3<>0,(ISNA(VLOOKUP(R1C3+2,Sheet6!R1:R1048576,1,0)))), IF(AND(R1C3<>0,(ISNA(VLOOKUP(R1C3+3,Sheet6!R1:R1048576,1,0)))),””,VLOOKUP(R1C3+3,Sheet6!R1:R" & MISSING_VARIABLE & ",1,0)),VLOOKUP(R1C3+2,Sheet6!R1:R1048576,1,0)),VLOOKUP(R1C3+1,Sheet6!R1:R1048576,1,0))))"

 
take a look at the bold bits