View Full Version : Solved: Help with my vlookup formula
genracela
05-17-2010, 05:28 PM
I have a file that the main purpose is to search a code from the other tab and return it's value in the Search tab.
I tried creating a Match Index and vlookup formulas but it doesn't work.
Please help.
Thanks!
genracela
05-17-2010, 06:10 PM
I already got the formula and the this VBA code works just fine.
But is there a way to enhance this code to make it faster?
Sub RF()
Dim lRow As Long
Application.Calculation = xlCalculationAutomatic
lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row
Range("B7:AW" & lRow).Formula = "=IFERROR(INDEX(CompiledData!C$2:C$65000,MATCH(1,INDEX(($A7=CompiledData!$A$ 2:$A$65000)*($A$3=CompiledData!$B$2:$B$65000)*(B$6=CompiledData!C$1),0),0)) ,0)"
Range("B7:AW" & lRow).Copy
Range("B7").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("L20").Select
MsgBox ("DONE")
Application.Calculation = xlCalculationAutomatic
End Sub
Thanks in advance!
austenr
05-17-2010, 07:43 PM
Your code is calculating every cell. Change calculation to manual at the top.
rbrhodes
05-18-2010, 02:45 AM
and...
with Application
.Calculation = xlCalculationmanual
.screenupdating = false
end with
'Your code
with Application
.Calculation = xlCalculationautomatic
.screenupdating = true
end with
genracela
05-18-2010, 02:46 AM
Thanks!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.