VBA- Linear Interpolation dont understand the inputs i should plug in

so i have a functionI'm new to VBA so any explanation will go a long way, Thank you.
my code takes an array and give out an array. So the function should takes the array and fills in the missing dates and rates. But when i put in the Linear interpolation formula it gives me an error, i think theres a probkem with my imputs. I'm having trouble with the linear interpolation formula mat(i,2) = the rates that i need to find and my interpolation formula.
i keep getting a value error.im very confused.
This is what I got so far:

Please Login or Register  to view this content.

is my

Please Login or Register  to view this content.

correct or am i using the wrong inputs? what would be my column 1 n 2? i used

Please Login or Register  to view this content.

n

Please Login or Register  to view this content.

to represent them.

When a UDF that does not have error trapping built into it returns a #VALUE error, this usually means that there is some kind of syntax or run time error in the UDF. I modified your UDF with several Stop statements to get into debug mode so I could step through the function, follow the logic, and hopefully identify what is going wrong.

Please Login or Register  to view this content.

You can see the stop statements, and you can see the statement that I think is causing the error. It is such a basic error, I will give you a chance to test that statement and see if you can identify the problem (hint, what do you get if you add a watch for mat(i,1)-mat(i,1) or put debug.print mat(i,1)-mat(i,1) into the immediate window?).

Without seeing this interpolation in context, I cannot say what the correction should be. Assuming you understand the context, I expect the correction will be obvious to you. If not, help us understand the context (perhaps upload a sample workbook with a sample input data set and the expected outcomes), and we should be able to help you.

As before, the first thing I did was add a bunch of stop statements to enter debug mode.

Please Login or Register  to view this content.

As near as I can tell, everything seems to work as it should except for the linear interpolation statement again. This statement looks wrong to me. I note that (Mat(i,1)-oldcount)/(oldcount-Mat(i,1)) in this expression will always be -1, which does not seem quite right. I would suggest that you revisit how you are defining your linear interpolation, work through the algebra again, because it seems like you are still missing something in the algebraic derivation of this statement. I did not think it through beyond that, assuming you know the interpolation formula you are trying to use here and can debug this statement.

An additional observation: it looks like you are trying to use oldcount2 to hold one of the fractions/percentages, but oldcount2 is dimensioned as an integer. I wonder if, among other corrections, oldcount2 should be dim'ed as a double.

An additional, thought, if it is not too much of a distraction from the above code -- is it necessary for you to use VBA for this? I note that your query here seems very similar to this one: https://www.excelforum.com/excel-gen...wo-points.html In this previous discussion, I recommended building a simple lookup table (with slopes and intercepts for each pair of points), then using that lookup table to perform the linear interpolation on the output table to fill in the missing entries. All of that is accomplished using fairly simple worksheet formulas. If VBA is not necessary, this might be an easier approach to solving your problem.