Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Insert VLOOKUP Formula In Cell Range Via VBA Macro

  1. #1
    Join Date
    11th August 2011
    Posts
    24

    Insert VLOOKUP Formula In Cell Range Via VBA Macro

    Hi, I'm trying to do a vlookup via a macro.

    Manually, I enter the following formula:

    =VLOOKUP(K2,'C:\Users\me\Documents\raw data\[current products to analyse.xlsx]Sheet1'!$A:$A,1,FALSE)

    and it works fine. Thus, I can then autofill down my sheet to calculate for many rows. Great.

    However, I'd like to automate that in a macro. So that I can leverage for different data inputs.

    I've tried the following:

    VB:
    Sub check_for_analysis() 
        Dim result As Variant 
         
         
         ' check_for_analysis Macro
         ' check_for_analysis using the VLOOKUP to external file
         
         
        Range("P1").Select 
        ActiveCell.FormulaR1C1 = "products to analyse" 
        Range("P2").Select 
        result = Application.VLookup(K2, "C:\Users\me\Documents\raw data\[current products to analyse.xlsx]Sheet1'!$A:$A", 1, False) 
        Range("P2").Select 
        Selection.AutoFill Destination:=Range("P2:P12669") 
        Range("P2:P12669").Select 
        Range("P1").Select 
        Selection.AutoFilter 
        ActiveWorkbook.Save 
    End Sub 
    
    
    but it doesn't work. After entering "products to analyse", nothing is entered into the range of P...

    If I change Application.VLookup to Application.WorksheetFunction.VLookup then I get other error messages..

    can someone please help suggest the best/simplest way to do this?

    Thanks.
    Last edited by Dave Hawley; February 14th, 2012 at 18:47.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,638

    Re: vlookup via VBA/Macro

    try
    change
    VB:
    Range("P2").Select 
    result = Application.VLookup(K2, "C:\Users\me\Documents\raw data\[current products to analyse.xlsx]Sheet1'!$A:$A", 1, False) 
    Range("P2").Select 
    Selection.AutoFill Destination:=Range("P2:P12669") 
    
    
    to
    VB:
    With Range("P2:P12669") 
        .Formula = "=VLookup(K2,'C:\Users\me\Documents\raw data\[current products to analyse.xlsx]Sheet1'!$A:$A,1,False)" 
        .Value = .Value 
    End With 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    11th August 2011
    Posts
    24

    Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

    Hi, it works!!! THANKYOU

    but 2 (related?) things:

    1. it causes excel to go into 100% CPU while making calculations for about 2 or 3 mins - basically freezing my machine while thinking about it (my pc is pretty recent).

    2. how can we make the 'autofil' range more dynamic, so that if I run the macro on another dataset file, it might only need to autofill 2000 rows - another might need 10000 etc. any quick fix there?

    thanks!!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,638

    Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

    Does this make any change?
    VB:
    With Range("P2") 
        .Formula = "=VLookup(K2,'C:\Users\me\Documents\raw data\[current products to analyse.xlsx]Sheet1'!$A:$A,1,False)" 
        With .Resize(Range("k" & Rows.Count).End(xlUp).Row - 1) 
            .FillDown 
            .Copy 
            .PasteSpecial xlPasteValues 
            End Eith 
        End With 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    11th August 2011
    Posts
    24

    Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

    no still freezes up quite a lot - but hey, the macro does the job so I'm happy

    ... was just wondering what might cause such a freeze - i can see in the bottom left, it says calculating 1% through to 100% and it takes its time... could it be an excel setting?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,638

    Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

    Then try set Calculation mode to manual and set back to automatic
    e.g

    VB:
    Sub ...() 
        Application.ScreenUpdating = False 
        Application.Calculation = xlCalculationManual 
         '
         ' your code
         '
        Application.Calculation = xlCalculationAutomatic 
        Application.ScreenUpdating = True 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th August 2011
    Posts
    24

    Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

    that just cripples the macro it seems... the cells are filled with #N/A, and that's it. so maybe your code NEEDS the autocalc enable to actually work??

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,638

    Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

    Yeah

    You need to delete the line of
    VB:
    .PasteSpecial xlPasteValues 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    11th August 2011
    Posts
    24

    Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

    ah, ok that lets it run again. This time it still freezes for 2 mins (@100% CPU).. but a 'different type' of freeze - this time the whole app is frozen, no chance to see the calculating message in the corner for example.

    maybe updating 10,000 rows this way is just very CPU intensive. works quickly when done manually though...

    unless there's another easy way to try this, I can just live with this 'side-effect' and we can call this case solved?!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. VBA Macro Code To Insert Formula Into Named Range
    By gararas in forum EXCEL HELP
    Replies: 5
    Last Post: July 2nd, 2010, 15:22
  2. Replies: 10
    Last Post: June 2nd, 2009, 23:54
  3. Macro Code To Insert Relative Formula Into Range
    By Andy3L in forum EXCEL HELP
    Replies: 5
    Last Post: June 17th, 2008, 13:15
  4. Replies: 8
    Last Post: April 8th, 2008, 12:13
  5. Insert Formula Into Range Macro
    By Karen337 in forum EXCEL HELP
    Replies: 5
    Last Post: October 7th, 2007, 13:35

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno