Good day everybody
I have a workbook with VLOOKUP formula..
I want to replace the formula with the code
Is it possible??
Good day everybody
I have a workbook with VLOOKUP formula..
I want to replace the formula with the code
Is it possible??
Why do you want to do that, the formula will be (far more) efficient.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Because the original file is very large - I think because of the formulas -
The workbook contains 100.000 record
If your lookup table is sorted, as it appears to be, the formula can be made more efficient by using ,TRUE instead of ,0
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Is there a way to convert the formulas to code?
Named ranges used for simplicity
Sub Macro2()
With Range("Price")
.FormulaR1C1 = "=VLOOKUP(RC[-2],Data,2,FALSE)"
.Value = .Value
End With
End Sub
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thank you very much Mr. mdmackillop
I'm pleased with your solution
Posting Permissions
Reply With Quote
