no matter what I am trying everything I do results in Excel adding an @ in front of the “Target!…” which destroys the formula. I am using the win32com Module and would like to keep it that way, but there has to be a way to get rid of this annoying behavior when pasting formulas.

This is my code snippet:

newOverviewSheet.Range("F2").Value = "=IFERROR(INDEX('Target'!$E:$E,MATCH(1,(A2='Target'!$B:$B)*(B2='Target'!$C:$C)*(E2='Target'!$A:$A),0)),"- %")"

And what I get is:

=IFERROR(INDEX(Target!$E:$E,MATCH(1,(A2=@Target!$B:$B)*(B2=@Target!$C:$C)*(E2=@Target!$A:$A),0)),"- %")

When I remove those @ manually, the formula works just fine.

I already tried replacing the @ with newOverviewSheet.Range("F2").Replace(What="@", Replacement="", LookAt=1) but sadly this would not work. I also tried replacing other parts of the formula so it is inserting a text and then afterwards puts the parts in making it a formula but this wouldn’t work to.

If I use the replace function within Excel it works. I recorded the macro and tried to “translate” the VBA Parameters over to Python, but I couldn’t get it to work. The macro that worked within Excel looks like this:

Cells.Replace What:="@", Replacement:="", LookAt:=xlPart, SearchOrder:= xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

I’ll keep trying, maybe I can answer myself, otherwise I hopes this is going to help someone having the same trouble.