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
|