how-to turn off the <@> symbol insert

%3CLINGO-SUB%20id%3D%22lingo-sub-2256993%22%20slang%3D%22en-US%22%3Ehow-to%20turn%20off%20the%20%26lt%3B%40%26gt%3B%20symbol%20insert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2256993%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20am%20programmatically%20inserting%20the%20following%20formula%3B%3CBR%20%2F%3E%3DIF(RC%5B1%5D%26gt%3B0%2CRC%5B1%5D%2CINDEX(distTTKitTKCtValue%2CMATCH(1%2C(RC%5B-1%5D%3DdistTTKitTkCtTT)*(RC%5B-29%5D%3DdistTTKitTKCtRMId)%2C0)))%3C%2FP%3E%3CP%3Emicrosoft%20is%20automatically%20adding%20the%20%26lt%3B%40%26gt%3B%20like%3B%3CBR%20%2F%3E%3D%40IF(RC%5B1%5D%26gt%3B0%2CRC%5B1%5D%2CINDEX(distTTKitTKCtValue%2CMATCH(1%2C(RC%5B-1%5D%3D%40distTTKitTkCtTT)*(RC%5B-29%5D%3D%40distTTKitTKCtRMId)%2C0)))%3C%2FP%3E%3CP%3Ethis%20returns%20an%20error%20%23N%2FA%20in%20my%20worksheet%3C%2FP%3E%3CP%3Ewhen%20i%20remove%20the%20%26lt%3B%40%26gt%3B%26nbsp%3B(manually)%20the%20formula%20returns%20the%20result%20fine%3C%2FP%3E%3CP%3Ewhy%20is%20this%20occurring%5C%20and%20might%20be%20a%20solution%20to%20this%20%26lt%3B%40%26gt%3B%26nbsp%3Bbusiness%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2256993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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?

 

3 Replies

@kent_culpepper 

Why it is added is here Implicit intersection operator: @ - Office Support (microsoft.com)

You may try this setting

image.png

if helps

@Sergei Baklan 

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.

 

Thanks!

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.

https://docs.microsoft.com/en-us/office/vba/excel/concepts/cells-and-ranges/range-formula-vs-formula...