Trying to drag down vba formula - getting global error

[Src016]

Trying to run the following function to drag a formula I insert into a cell:

Dim LastRowMaster As Long

Sheets("Master").Select


numRows = Range("A1", Selection.End(xlDown)).Rows.Count
LastRowMaster = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row


Range("j2").Select
ActiveCell.Formula = "=IFNA(IF(VLOOKUP(E2,Responses!E:R,6,FALSE)=0,"""", (VLOOKUP(e2,Responses!E:R,6,FALSE))),"""")"
Range("j2").Select
Selection.AutoFill Destination:=Range("j2:j" & LastRowMaster)
'Range("J2").Select


Getting a run-time error 1004

Method 'range' of object'_Global'failed

Any ideas?

V/r,
Spencer

 

[JoeMo]

You didn't tell us what line causes the error, but the formula doesn't have the correct syntax. Did you mean something like this?

ActiveCell.Formula = _
    "=IF(ISNA(VLOOKUP(E2,Responses!E:R,6,FALSE)),"""", VLOOKUP(e2,Responses!E:R,6,FALSE))"

 

[gravanoc]

Src, did you place your code inside a Sub?

Public Sub DoThis()

'Code

End Sub

 

[LMacD]

I ran your code on Excel2010 just as you posted it. I had no issues.

Try it again but fully qualify the range [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sheets("Master").Range("J2").Select

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Option Explicit
Sub test()
    Dim LastRowMaster As Long
    Dim numRows As Long
    Sheets("Master").Select
    
    numRows = Range("A1", Selection.End(xlDown)).Rows.Count
    LastRowMaster = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
    
    Sheets("Master").Range("j2").Select
    ActiveCell.Formula = "=IFNA(IF(VLOOKUP(E2,Responses!E:R,6,FALSE)=0,"""", (VLOOKUP(e2,Responses!E:R,6,FALSE))),"""")"
    Sheets("Master").Range("j2").Select
    Selection.AutoFill Destination:=Range("j2:j" & LastRowMaster)
    Sheets("Master").Range("J2").Select
End Sub[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][/FONT]

[/FONT]

 

[Src016]

Thank you so much! That was the porblem exactly. I was stepping through the code, made the edits, and forgot to step through the part that fully qualifies the range Sheets("Master").Range("J2").Select

Thanks again.

 

[Src016]

Thank you! This formula actually works significantly better paired with the code that drags it down. This forum is the best!