I have a formula I am inserting into a cell in excel. After the formula is inserted it gives me a #VALUE! in the cell as an error. So when I inspect the formula I notice it is inserting an @ in my formula. When I manually remove the @ the formula works and returns a correct value.
The formula I am using in VBA is:
=IF($E2="""","""",IF($F2=0,(LOOKUP(2,1/($H2:$M2<>""""),$H2:$M2))*$G2,IF($F2>0,INDEX($H2:$M2,MATCH(TRUE,""""<>($H2:$M2),0))*$F2+(LOOKUP(2,1/($H2:$M2<>""""),$H2:$M2)*$G2),"""")))
and the formula looks like this after it is inserted in the cell: (Notice the @ character in the Match function)
=IF($E2="""","""",IF($F2=0,(LOOKUP(2,1/($H2:$M2<>""""),$H2:$M2))*$G2,IF($F2>0,INDEX($H2:$M2,MATCH(TRUE,""""<>(@$H2:$M2),0))*$F2+(LOOKUP(2,1/($H2:$M2<>""""),$H2:$M2)*$G2),"""")))
could someone explain why this is happening and maybe how to prevent it. this code is part of a larger code if you need more please let me know. thanks for your help in advance.
