Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP equation in VBA
Hi all,
Looking for help creating an equivalent VBA public function statement for the following VLOOKUP formula: =VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)+(L11-VLOOKUP(L11,'Sheet'!$I$2:$K$19,1))/VLOOKUP(L11,'Sheet'!$I$2:$K$19,3)*(VLOOKUP(VLOOKUP (L11,'Sheet'!$I$2:$K$19,1)+VLOOKUP(L11,'Sheet'!$I$ 2:$K$19,3),'Sheet'!$I$2:$K$19,2)-VLOOKUP(L11,'Sheet'!$I$2:$K$19,2) Thanks in advance for everyone's help, Ed |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP equation in VBA
That specific formula
Public function MySpecialLookup() s = "VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)+" s1="(L11-VLOOKUP(L11,'Sheet'!"$I$2:$K$19,1))/" s2="VLOOKUP(L11,'Sheet'!$I$2:$K$19,3)*(VLOOKUP(" s3="VLOOKUP(L11,'Sheet'!$I$2:$K$19,1)+VLOOKUP(" s4="L11,'Sheet'!$I$2:$K$19,3),'Sheet'!$I$2:$K$19,2 )" s5 = "-VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)" MySpecialLookup = Evaluate(s & s1 & s2 & s3 & s4 & s5) End Function -- Regards, Tom Ogilvy "Ed" wrote: Hi all, Looking for help creating an equivalent VBA public function statement for the following VLOOKUP formula: =VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)+(L11-VLOOKUP(L11,'Sheet'!$I$2:$K$19,1))/VLOOKUP(L11,'Sheet'!$I$2:$K$19,3)*(VLOOKUP(VLOOKUP (L11,'Sheet'!$I$2:$K$19,1)+VLOOKUP(L11,'Sheet'!$I$ 2:$K$19,3),'Sheet'!$I$2:$K$19,2)-VLOOKUP(L11,'Sheet'!$I$2:$K$19,2) Thanks in advance for everyone's help, Ed |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP equation in VBA
You're missing a closing parenthesis somewhere. I assume it's at the
end, since otherwise the first term and last term would cancel out. Assuming you want the function, e.g., to call =VLOOKUP(L11,rng,2) + (L11 - VLOOKUP(L11,rng,1)) / VLOOKUP(L11,rng,3) * (VLOOKUP(VLOOKUP(L11,rng,1) + VLOOKUP(L11,rng,3),rng,2) - VLOOKUP(L11,rng,2)) as =ComplexVLookup(L11, Sheet!I2:K19) This could be replaced by Public Function ComplexVLookup( _ ByVal what As Variant, rng As Range) As Variant Dim dOne As Double Dim dTwo As Double Dim dThree As Double Dim dFour As Double On Error GoTo ErrorHandler With Application dOne = .VLookup(what, rng, 1, True) dTwo = .VLookup(what, rng, 2, True) dThree = .VLookup(what, rng, 3, True) dFour = .VLookup(dOne + dThree, rng, 2, True) End With ComplexVLookup = dTwo + (what - dOne) / dThree * (dFour - dTwo) ResumeHe Exit Function ErrorHandler: ComplexVLookup = CVErr(xlErrNA) Resume ResumeHere End Function In article , Ed wrote: Hi all, Looking for help creating an equivalent VBA public function statement for the following VLOOKUP formula: =VLOOKUP(L11,'Sheet'!$I$2:$K$19,2)+(L11-VLOOKUP(L11,'Sheet'!$I$2:$K$19,1))/VLO OKUP(L11,'Sheet'!$I$2:$K$19,3)*(VLOOKUP(VLOOKUP(L1 1,'Sheet'!$I$2:$K$19,1)+VLOO KUP(L11,'Sheet'!$I$2:$K$19,3),'Sheet'!$I$2:$K$19,2 )-VLOOKUP(L11,'Sheet'!$I$2:$ K$19,2) Thanks in advance for everyone's help, Ed |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup equation on multiple tabs | Excel Discussion (Misc queries) | |||
I need Equation | Excel Discussion (Misc queries) | |||
Vlookup equation for multiple tabs | Excel Discussion (Misc queries) | |||
Vlookup and Hlookup equation | Excel Discussion (Misc queries) | |||
Equation Editor- problem when editing an equation | Excel Discussion (Misc queries) |