Vlookup error

Hi,

I am trying to put a VLookup formula using VBA.

The code is as follows:

Sub testingV()
Dim ws9 As Worksheet
Dim rng9 As Range

Set ws9 = Sheets("Sheet5")
Set rng9 = ws9.Range("C2", ws9.Range("C" & ws9.Rows.Count).End(xlUp))

rng9.Offset(, 7).Formula = "=IF(ISNA(VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$500,3,FALSE)),"", VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$500,3,FALSE))"

With rng9.Offset(, 9)
.Formula = "=IF(I1),1,""x"")"
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Select
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
End With
rng9.Offset(, 7).Resize(, 4).ClearContents
End Sub


However, when i am executing the same i am getting an error message at line:

rng9.Offset(, 7).Formula = "=IF(ISNA(VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$500,3,FALSE)),"", VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$500,3,FALSE))"

The error message reads :
"Run Time Error 1004"
"Application defined or Object defined error"

Someone please guide me where m i doing wrong.

Thanks

Sub testingV()
Dim ws9 As Worksheet
Dim rng9 As Range

Set ws9 = Sheets("Sheet5")
Set rng9 = ws9.Range("C2", ws9.Range("C" & ws9.Rows.Count).End(xlUp))

rng9.Offset(, 7).Formula = "=IF(ISNA(VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$500,3,FALSE)),"", VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$500,3,FALSE))"

With rng9.Offset(, 9)
.Formula = "=IF(I1),1,""x"")"
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Select
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete
End With
rng9.Offset(, 7).Resize(, 4).ClearContents
End Sub

someone please help ....