PDA

View Full Version : Solved: Replace Formula with code



YasserKhalil
10-21-2010, 01:34 AM
Good day everybody
I have a workbook with VLOOKUP formula..
I want to replace the formula with the code
Is it possible??

Bob Phillips
10-21-2010, 02:23 AM
Why do you want to do that, the formula will be (far more) efficient.

YasserKhalil
10-21-2010, 02:51 AM
Because the original file is very large - I think because of the formulas -
The workbook contains 100.000 record

Bob Phillips
10-21-2010, 03:06 AM
If your lookup table is sorted, as it appears to be, the formula can be made more efficient by using ,TRUE instead of ,0

YasserKhalil
10-21-2010, 02:23 PM
Is there a way to convert the formulas to code?

mdmackillop
10-21-2010, 02:30 PM
Named ranges used for simplicity

Sub Macro2()
With Range("Price")
.FormulaR1C1 = "=VLOOKUP(RC[-2],Data,2,FALSE)"
.Value = .Value
End With
End Sub

YasserKhalil
10-21-2010, 06:46 PM
Thank you very much Mr. mdmackillop
I'm pleased with your solution