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