#VALUE! error on vba function with named range vlookup

Question

I have the following functions in VBA:

Function weightedAverage(x0, y0, x1, y1, x)
     weightedAverage = y0 + (x - x0) * (y1 - y0) / (x1 - x0)
End Function

Function interpolateLookup(lookupVal As Range, lookupRange As Range, colID As Integer) As Double
     step = 0.01

     x0 = Application.Round(lookupVal.Value, 3)
     y0 = Application.VLookup(x0, lookupRange, colID, True)

     x1 = Application.Round(lookupVal.Value, 3) + step
     y1 = Application.VLookup(x1, lookupRange, colID, False)

     x = lookupVal.Value

     interpolateLookup = weightedAverage(x0, y0, x1, y1, x)
End Function

The problem here is that my interpolateLookup function returns a #VALUE! error when I pass it the following input:

=interpolateLookup(E5,K3:O803,5)

Why???

Have you stepped through the code? My first thought would be that either y0 or y1 are returning an error value. — Rory
– Rory, Commented Dec 21, 2017 at 8:50

Vasil Yordanov · Accepted Answer · 2017-12-21 14:12:13Z

The real problem was in my head. Found a solution by exploiting the precision of the keys for my lookup:

Function interpolateLookup(lookupVal As Range, lookupRange As Range, colID As Integer) As Double
     step = 0.01

     x0 = Application.Round(lookupVal.Value, 2)
     y0 = Application.VLookup(x0, lookupRange, colID, False)

     x1 = Application.Round(lookupVal.Value, 2) + step
     y1 = Application.VLookup(x1, lookupRange, colID, False)

     x = lookupVal.Value

     interpolateLookup = Application.Round(weightedAverage(x0, y0, x1, y1, x), 1)
End Function

My lookupVal has precision of 3 digits and my lookup keys are with precision with 2 digits, thus I rounded my lookupVal to 2 digits, knowing that I will have an exact match in the table plus also knowing that it will be my lower band for the interpolation.

Thanks for the comments!

Collectives™ on Stack Overflow

#VALUE! error on vba function with named range vlookup

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related