Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Insert VLOOKUP Formula In Cell Range Via VBA Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:

    Code:
    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, 18:47.

  • #2
    Re: vlookup via VBA/Macro

    try
    change
    Code:
        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
    Code:
        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

    Comment


    • #3
      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!!

      Comment


      • #4
        Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

        Does this make any change?
        Code:
        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

        Comment


        • #5
          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?

          Comment


          • #6
            Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

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

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

            Comment


            • #7
              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??

              Comment


              • #8
                Re: Insert VLOOKUP Formula In Cell Range Via VBA Macro

                Yeah

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

                Comment


                • #9
                  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?!

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X