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]

Try this:

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").[B][COLOR=#ff0000]Range("P2:P" & lRow)[/COLOR][/B]
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

 

[WxShady13]

Thank you!!!!

 

[DanteAmor]

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

 

[SpillerBD]

Shouldn't the VLOOKUP function just be wrapped in an IFERROR function?

 

[WxShady13]

Question Sir...For some reason when I run the code it does not stop at the last line of the table. It just continues on, any ideas?

 

[DanteAmor]

The last line is:

[COLOR=#333333][I]lRow = Cells(Rows.Count, 1).End(xlUp).Row[/I][/COLOR]

check if in column A you have blank cells facing down

 

[WxShady13]

No that column is full of the same information.

 

[DanteAmor]

Perform the test with 3 records in column A and execute with F8 step by step the macro and tell me the line number stored in the variable lrow

 

[WxShady13]

Sir THANK YOU for troubleshooting...What I have found is it runs fine, just a RAM issue on my computer doing the calculations. Sir a thank you truly isn't enough, you saved me a ton of work!