Forum Discussion
Function does not work beyond column AA and row 100
Hello
I have created a function to interpolate x-y data.. the function works fine but there are two problems with the function...
1. Each time I reopen the worksheet the values do not update. I have to type enter in each box to update the values
2. more annoying: the function doesn't work beyond rows 100 and columns AA. I get the #VALUE error message. I have large spreadsheets and I am tired of copying the data to rows < 100 and columns < AA
Spreadsheet has been attached
The function is :
Function Yint(Xdata As Range, Ydata As Range, Xint As Range) As Double
Yint = Evaluate("=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},INDEX(" & Xdata.Address & ",MATCH(" & Xint.Address & "," & Xdata.Address & ")+N(IF(1,{-1;0;1;2})))-" & Xint.Address & ")))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},INDEX(" & Ydata.Address & ",MATCH(" & Xint.Address & "," & Xdata.Address & ")+N(IF(1,{-1;0;1;2})))))/2")
End Function
Anyone can help?
Thanks
Mark
Mark
Hi Mark,
The length of the string in Evaluate shall be not more than 255 characters. Below AA and 100 you have exactly 255 if I calculated correctly, one more symbol in address gives an error.
You may add to your code
strMatchFirst = Evaluate("=MATCH(" & Xint.Address & "," & Xdata.Address & ")")and CStr(strMatchFirst) in your main formula instead of above MATCH combinatian. Attached
Hi Mark,
The length of the string in Evaluate shall be not more than 255 characters. Below AA and 100 you have exactly 255 if I calculated correctly, one more symbol in address gives an error.
You may add to your code
strMatchFirst = Evaluate("=MATCH(" & Xint.Address & "," & Xdata.Address & ")")and CStr(strMatchFirst) in your main formula instead of above MATCH combinatian. Attached
Hi Sergey
WOW ! Thanks for your help !
Mark
The VBA Evaluate() Function has a 255 character limit. That's your issue.
*Edit: That's odd when I posted my answer no responses were displayed. I can see Sergei answered this question nearly an hour ago....
