Forum Discussion

Mark Bogstad's avatar
Mark Bogstad
Copper Contributor
Jul 28, 2018
Solved

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...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 28, 2018

    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

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jul 28, 2018

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....

Resources