VBA Formula for vlookup not calculating

teatimecrumpet

Active Member
Joined
Jun 23, 2010
Messages
307
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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:

Code:
[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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top