VBA Formula Error

[BigNate]

Hello Everyone,

I am getting a run time error '1004' because the syntax in the last line (ActiveCell.Formula line) of my code is not right. Can someone please tell me what I am doing wrong?

Sheets("Status").Select
Cells(2, 3).Select
Refcolumnend = Sheets("Reference").Cells(1, Columns.Count).End(xlToLeft).Column
ActiveCell.Formula = "=IF(AND(Reference!" & Cells(Refcolumnend, 3).Address(True, True) & "=PROPER(VLOOKUP(Macro!B1,Dates!$N$3:$O$14,2,0)),COUNTBLANK(Reference!" & Cells(4, Refcolumnend).Address(True, True) & ":" & Cells(8, Refcolumnend).Address(True, True) & ",""SUCCESSFUL"",""FAILED"")"

Thanks!

 

[BobUmlas]

The last part of your formula comes out to be
COUNTBLANK(Reference!$G$4:$G$8,"SUCCESSFUL","FAILED")
and should be something like
COUNTBLANK(Reference!$G$4:$G$8)=0),"SUCCESSFUL","FAILED")

 

[BigNate]

Bob, thanks for your help. I added in the last part of the formula like you suggested and all is good now. Thanks again!