i am programmatically inserting the following formula; =IF(RC[1]>0,RC[1],INDEX(distTTKitTKCtValue,MATCH(1,(RC[-1]=distTTKitTkCtTT)*(RC[-29]=distTTKitTKCtRMId),0)))
microsoft is automatically adding the <@> like; =@IF(RC[1]>0,RC[1],INDEX(distTTKitTKCtValue,MATCH(1,(RC[-1]=@distTTKitTkCtTT)*(RC[-29]=@distTTKitTKCtRMId),0)))
this returns an error #N/A in my worksheet
when i remove the <@> (manually) the formula returns the result fine
why is this occurring\ and might be a solution to this <@> business?
Thanks for reply. Yes, I read that article before posting. There was another similar post that suggested the turning off of an option but it was not obvious to me which option. I will go ahead and try that setting.
In the meantime, I found a work-around that seemed to work.. which was using the "FILTER" variation of the formula which, yes still adds the @ symbol. But actually returns the expected result, instead of an error. So, its all very interesting.
Also, if your formula is targeting newer excel versions that use dynamic arrays and not the legacy implicit intersection method, then I believe you should be using the Formula2 property of the range object and not the formula property.
You are correct. In a macro I was creating I had to use .formula2 and .value2. If I didn't when it returned a formula in text string format back to the formula bar in another cell it added the @ symbol because I was using dynamic arrays in excel 365. It gave an #N/A error when it inserted the symbol.
Very similar issue for me, with unwelcome "@" inserted into two dynamic arrays (using OFFSHOOT) where I was dividing one array into the other. After reading your post, I switched to .formula2 and problem was solved immediately. Keep up the good work!
