Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: VBA Formula for vlookup not calculating

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    307
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Formula for vlookup not calculating

    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
    I'm the ultimate Noob Trying to learn VBA.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Formula for vlookup not calculating

    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:
    Sub Foo()
    
    'Insert New column N and write Formula
    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)"
    
    '//Copy formula as far as values exist in adjacent column M
    Range("N2").Copy
    Range("N2:N" & Range("M65536").End(xlUp).Row).PasteSpecial
    
    End Sub
    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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Posts
    307
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Formula for vlookup not calculating

    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!
    I'm the ultimate Noob Trying to learn VBA.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com