@ Symbol appearing for the index/match vba code

[aaleem]

HI,
i have VBA code which is working fine, however, for this line of code, the resulting formula is coming with the "@" symbol thus causing invalid results.

 dataSheet.Cells(i, "AG").Formula = "=IF(F" & i & "=AF" & i & ",1,IFERROR(INDEX(Master!$AO:$AO,MATCH(1,(F" & i & "=$AK:$AK)*(AF" & i & "=$AL:$AL),0)),""Not Found""))"

The resulting formula is =IF(F2=AF2,1,IFERROR(INDEX(Master!$AO:$AO,MATCH(1,(F2=@$AK:$AK)*(AF2=@$AL:$AL),0)),"Not Found")).

Can some one please advise how we can correct this.

regards
aleem

 

[James006]

Hi,

With the new Dynamic Array formulas, you do enjoy the now famous SPILL effect ... !!!
@ is the Implicit Intersection Operator
see explanation :

Implicit intersection operator: @ - Microsoft Support

Learn about the implicit intersection operator (@), which returns a single value using logic known as implicit intersection. The implicit intersection operator (@) may return a value, single cell range, or an error. The implicit intersection operator (@) is in a class of functions called dynamic...

support.microsoft.com

 

[JvdV]

You should, as explained here, use:

.Formula2

 

[aaleem]

Thank you James and JvdV for highlighting the new dynamic arrays, I have now updated the VBA code as below. this is working fine.

dataSheet.Cells(i, "AG").FormulaArray = "=IF(F" & i & "=AF" & i & ",1,IFERROR(INDEX(Master!$AO:$AO,MATCH(1,IF(F" & i & "=Master!$AK:$AK,IF(AF" & i & "=Master!$AL:$AL,1)),0)),""Not Found""))"