Insert VLOOKUP Formula In Cell Range Via VBA Macro

[egg]

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:

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.

[jindon]

try
change

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

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 

[egg]

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!!

[jindon]

Does this make any change?

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 

[egg]

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?

[jindon]

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

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

[egg]

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

[jindon]

Yeah

You need to delete the line of

.PasteSpecial xlPasteValues 

[egg]

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?!