Results 1 to 3 of 3

VBA Formula for vlookup not calculating

This is a discussion on VBA Formula for vlookup not calculating within the Excel Questions forums, part of the Question Forums category; Hi, I'm using some vba code to insert a column and then place a formula for a vlookup. But the ...

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    290

    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
    13,906

    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 2007/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
    290

    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.

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
  •  


DMCA.com