VBA Formula for vlookup not calculating

[teatimecrumpet]

Hi,

I'm using some vba code to insert a column and then place a formula for a vlookup. But the resulting cells with formulas are not calculating instead it is showing up as a string.

Also, as a test I could not place in a basic formula into thos cells after the script ran: I put in "=2+2" and it would just who it as a string.

Now if input that basic formula elsewhere in the sheet it's ok.

I am using Excel 2007 and also tried to use the Formula ribbon to calculate the sheet both manually and automatically but nothing.


Does this have something to do with using the "Filldown" operation?

As always thanks!

here's my code:
'add column for vlookup of names

Columns("N").Insert
Range("N1").Formula = "Names"
Range("N2").Formula = "=VLOOKUP(M2,'C:\Users\teatimecrumpet\Desktop\[_FINAL_Name_ITEMS_VBA.xlsx]UniQ-Names-Final'!$E$1:$G$51,3,FALSE)"
Range("M65536").End(xlUp).Offset(0, 1).Select
Selection.Formula = "1"
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown

 

[xenou]

If formatting is text (as it must be) this can happen. Perhaps that's just coincidental due to some leftover formatting you didn't mean to keep - but if you continue to have this problem just set the formatting before writing in the formula.

I don't use Fill (for no particular reason) and or Select very often so my sample takes a slightly different approach:

[COLOR="Navy"]Sub[/COLOR] Foo()

[COLOR="SeaGreen"]'Insert New column N and write Formula[/COLOR]
Columns("N").Insert
Range("N1").Formula = "Names"
Range("N2").NumberFormat = "General"
Range("N2").Formula = "=VLOOKUP(M2,'C:\Users\teatimecrumpet\Desktop\[_FINAL_Name_ITEMS_VBA.xlsx]UniQ-Names-Final'!$E$1:$G$51,3,FALSE)"

[COLOR="SeaGreen"]'//Copy formula as far as values exist in adjacent column M[/COLOR]
Range("N2").Copy
Range("N2:N" & Range("M65536").End(xlUp).Row).PasteSpecial

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

This is untested - I hope it works for you. If not, I'll see if I can test this more rigorously at home with XL2007

 

[teatimecrumpet]

Thanks for getting back to me xenou. I was actually just about to post about the formatting. I manually changed it to "General" and it worked out ok.

I haven't tested your code out yet. But will do soon.

Thanks again!