Excel 2010 :: VLookup Will Not Match Unless Use Sendkeys F2

We have a business system which has some very disjointed reporting. To fix this I have multiple extracts that are then combined into a single report. I then add Year & Period from a lookup of a date in a particular field. Unfortunately most values are returned as #N/A but if I manually go into the sheet and select an offending cell and click then magically the formula then corrects itself.

So I thought I would be clever and use Sendkeys {F2} as part of my code. Unfortunately this is a bit hit & miss as it works on some but not on others. Also the Sendkeys piece repeats itself leaving my frontsheet a 1000 rows from the start. This is easily fixed by selecting cell A1as the last step.

My question is there methods in VBA by not using sendkeys that will achieve the same result and is consistent. I am using Excel 2010.

Code:
Sub Add_Calendar()
Dim cell As Object, c As Range

'setup for calendar data population
' Range("H1") = "Created Date"
Range("N1") = "Year"
Range("O1") = "Period"
'copy the format

[Code] ........