Help with pasting formulas using VBA

[madsnielsen]

Currently doing a project at work, where I am making a standard document, people should be able to make changes to. I am therefore in a situation where I would like for the "Vlookup" function to be pasted. However when using:

rawpurchase.Cells(i, 6) = "=vlookup(E" & i & ",'PurchaseOverview'!A1:C" & nrowover & " ,3,False)"

I am only getting a #name in the field. When entering the field and pressing enter, the results shows. I have tried different solutions, however so far only search for “=” and replace with “=” works, but doing the exact same operation through a macro does not do the job. Also deleting “=” from the celland inserting “=” again, does not change anything either.
I would very much like to avoid anything like the use of send keys etc.
Any help would be much appreciated ?

 

[Fluff]

Should there be a space between Purchase & Overview in the Vlookup?

 

[madsnielsen]

Yes, the "Purchase Overview" is a reference to another sheet. And I would expect a mistake in this to return the wrong value of the lookup function - which is not the case :/

 

[MARK858]

No it would produce a name error as it can't find the sheet to reference the range but it doesn't explain why it would correct itself by just pressing enter.

 

[Fluff]

I agree with Mark, but what happens if you add the space?

 

[madsnielsen]

I am sorry. Did not see that the space was missing from the code I printed here. It is in the original code.

 

[Fluff]

In that case have you tried

rawpurchase.Cells(i,6).Formula = "= vlookup(E" & i & ",'Purchase Overview'!A1:C" & nrowover & " ,3,False)"

as previously suggested?

 

[madsnielsen]

Yes your line does run, but does not change the outcome unfortunately. Both with and without the space in front of the "="

 

[madsnielsen]

No ! You were right. That line did work. Thanks you so much. Very much appreciated !

 

[Fluff]

Glad we could help & thanks for the feedback