Dear all
i want to apply a function for specific range of column cells so i write this macro but it's keep saying type mismatch and i couldn't find where is the error
the formula working fine when i apply it manually to the cells.
your help is highly appreciated

Sub ResetSheet()

Range("S4:S500").Formula = "=IF(Q4="";VLOOKUP(CONCATENATE(O4;" - ";P4);'C:\DOCUME~1\lgriffin\LOCALS~1\Temp\notesBAAA25\[Warden List 08 July 2010.xls]Rooms'!$A$2:$B$300;2;FALSE);VLOOKUP(Q4;Rooms!$A$300:$B$500;2;FALSE))"


End Sub

and also i tried another way with the same problem as following


Sub ResetSheet()
With Range("S4")
.Formula = "=IF(Q4="";VLOOKUP(CONCATENATE(O4;" - ";P4);'C:\DOCUME~1\lgriffin\LOCALS~1\Temp\notesBAAA25\[Warden List 08 July 2010.xls]Rooms'!$A$2:$B$300;2;FALSE);VLOOKUP(Q4;Rooms!$A$300:$B$500;2;FALSE))"
.AutoFill Destination:=Range(.Offset(0, -1), .Offset(0, -1).End(xlDown)).Offset(0, 1)
End With
End Sub