VBA VLOOKUP Convert to Values Gives #N/A

I'm having some trouble with VLOOKUP in my VBA. Here's an example of the code I'm using:

Sub Macro15()
'
' Macro15 Macro
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:B" & LR).FormulaR1C1 = _
"=VLOOKUP(RC[-1],'https://internal_sharepoint_address
    /[Vendor_Information.xlsx]Sheet1'!R3C3:R150C18,4,FALSE)"
Range("C1:C" & LR).FormulaR1C1 = _
    "=VLOOKUP(RC[-2],'https://internal_sharepoint_address
/[Vendor_Information.xlsx]Sheet1'!R3C3:R150C18,5,FALSE)"
With Range("B1:C" & LR)
.Value = .Value
End With
End Sub

The problem is that the values in Columns B & C (the VLOOKUP formulas) return a value of #N/A.

However, if I stop the code before converting the formula to valu开发者_StackOverflow中文版es (the "With Range("B1:C" & LR)" line), the VLOOKUP formula returns the correct values.

Also strange - if I clear the contents of Columns B & C and re-run the above code, the values return fine. If I try to add a second cycle to the VBA, however, it does NOT work.

Any wisdom that anyone can provide would be a huge help. I've been stuck on this for a long time, and I'm just at my wit's end.

Thanks all, David

---

You'll probably need to add in a step that runs a calculation cycle before you try to replace with the value:

Application.Calculate

Edit from comment: I would imagine that retrieving lookup data from a linked workbook on a Sharepoint site would take awhile. Maybe add some delay loops? Can you make two separate macros (one ending with the formulas, and a second one starting at the Paste Values), and run them separately with a pause in between?