Pasting a formula into a dynamic table column

[WxShady13]

I am pasting a Vlookup formula into a table (information) that is dynamic. The formula goes fine, however I run code after that to replace #N/A with a 0. I have a line of code that finds the last row, however all my #N/A code does is find the last cell and place a 0 in it.

Sub VlookupFormula()
Dim lRow As Long
Dim ws As Worksheet
Range("P1").Select
ActiveCell.FormulaR1C1 = "Vlookup Amount"
Range("P2").Formula = "=VLOOKUP(O2,Table3[#All],4,FALSE)"
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Set NARange = Worksheets("Data").Range("P2" & lRow)
Worksheets("Data").Activate
For Each CheckCell In NARange
    If Application.WorksheetFunction.IsNA(CheckCell) Then
         'CheckCell.Offset(0, 1).Copy
         Worksheets("Data").Range("Y1").Copy
         CheckCell.Select
         Worksheets("Data").Paste  'Doing this rather than value will bring formatting
    End If
Next CheckCell
End Sub

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.