VBA Works with pasting VLOOKUP formula but not value

[bquek]

I need help to resolve the problem of why the VBA for pasting formula works fine but not for pasting value which result with #VALUE!

Below are the abstract two VBA lines. Any suggestion on what to change or modify.

1. Pasting formula works fine.
Cells(.Row, "AC").Formula = "=IFERROR(IF(VLOOKUP(IF(ISERR(P" & .Row & "+0),P" & .Row & ",P" & .Row & "+0),Table1[[Invoice Number]:[Doc ID]],11,False)=0,IF(VLOOKUP(P" & .Row & ",Table1[[Invoice Number]:[Doc ID]],11,TRUE)=0,""RRqst"",(VLOOKUP(P" & .Row & ",Table1[[Invoice Number]:[Doc ID]],11,TRUE))),VLOOKUP(IF(ISERR(P" & .Row & "+0),P" & .Row & ",P" & .Row & "+0),Table1[[Invoice Number]:[Doc ID]],11,False)),"""")"

2. Pasting value returns to #VALUE! [I just change the .Formula = "..." to .Value=Evaluate(...) and get #VALUE!]
Cells(.Row, "AC").Value = Evaluate("=IFERROR(IF(VLOOKUP(IF(ISERR(P" & .Row & "+0),P" & .Row & ",P" & .Row & "+0),Table1[[Invoice Number]:[Doc ID]],11,False)=0,IF(VLOOKUP(P" & .Row & ",Table1[[Invoice Number]:[Doc ID]],11,TRUE)=0,""RRqst"",(VLOOKUP(P" & .Row & ",Table1[[Invoice Number]:[Doc ID]],11,TRUE))),VLOOKUP(IF(ISERR(P" & .Row & "+0),P" & .Row & ",P" & .Row & "+0),Table1[[Invoice Number]:[Doc ID]],11,False)),"""")")

Thank you for any recommendation or suggestion.
Cheers,
Ben

 

[AkaTrouble]

Application.WorksheetFunction might help

https://msdn.microsoft.com/en-us/library/office/ff834434.aspx

[url]https://msdn.microsoft.com/en-us/library/office/ff194701.aspx
[/URL]