@ is showing up in formula through vba - need to stop it

[Jim885]

I've spent some time researching this but haven't been able to come close to finding an answer. So maybe someone here can help.

My office is using Excel 365 in Windows 10

I ran the below lines of code, then the "@" sign is showing up as part of the formulas on the excel spreadsheet, and causing erroneous formula results.
I would like to prevent Excel 365 from doing this, or come up with a solution in vba to eliminate this from '@' appearing in the spreadsheet formula.

Hope someone can help.

The vba code I have:
ws2.Range("K2:K" & iRow).Formula = "=SUM(IF($D$2:$D2=D2,($H$2:$H2),0))"
ws2.Range("M2:M" & iRow).Formula = "=IFERROR(SUM(IF($D$2:$D2=D2,($H$2:$H2)/E2,0)),""-"")"

The formula I expected to appear in the cells;

=SUM(IF($D$2:$D2=D2,($H$2:$H2),0))
=IFERROR(SUM(IF($D$2:$D2=D2,($H$2:$H2)/E2,0)),"-")


The formula that appears in the cells and produces miscalculations;
=SUM(IF($D$2:$D2=@D2,($H$2:$H2),0))
=IFERROR(SUM(IF(@$D$2:$D2=D2,(@$H$2:$H2)/E2,0)),"-")


The first time this happened today, I got a quick "popup" that said this is new, and the @ would not affect the formula result ... But it has

Does anyone have an idea how to eliminate this @ problem from appearing in the formula?

Thanks.

 

[VBE313]

 

[fhqwgads]

reportedly people have been just ctrl H replacing any @'s with blank and getting formulas to work as intended

 

[i_nth]

Your formulae don't make sense. The main issue is that you're comparing a range with a value. E.g. by the time the formulae are copied to row 20 they say, in part, $D$2:$D20=D20. This returns an array of 19 values, the first 18 of which are FALSE and the last one is TRUE.
This is unlikely to be what you intended. More generally, most of each formula isn't actually doing anything useful. Consequently, they can be greatly simplified:
=SUM(IF($D$2:$D2=D2,($H$2:$H2),0)) is equivalent to =H2
=IFERROR(SUM(IF($D$2:$D2=D2,($H$2:$H2)/E2,0)),"-") is equivalent to =IFERROR(H2/E2,"-")

 

[MARK858]

=SUM(IF($D$2:$D2=D2,($H$2:$H2),0)) is equivalent to =H2
=IFERROR(SUM(IF($D$2:$D2=D2,($H$2:$H2)/E2,0)),"-") is equivalent to =IFERROR(H2/E2,"-")

@i_nth, If dealing with a single cell holding the formula the above is correct but what happens if you drag the formulas down? I get the below for the first formula (please note that the implicit operator doesn't show with the XL2BB app yet)...

Book1
	D				H			K	L	M
1								With implicit	w/o implicit	i_nth
2	3				3			3	3	3
3	2				4			7	4	4
4	5				5			12	5	5
5	3				1			13	4	1
6	2				3			16	7	3
7	3				2			18	6	2
8	3				4			22	10	4
9	3				3			25	13	3
10	2				5			30	12	5
11	3				5			35	18	5
Sheet1

Cell Formulas
Range	Formula
K2:K11	K2	=SUM(IF($D$2:$D2=D2,($H$2:$H2),0))
L2:L11	L2	=SUM(IF($D$2:$D2=D2,($H$2:$H2),0))
M2:M11	M2	=H2

 

[MARK858]

@Jim885, playing around with it if you put it in VBA as .FormulaArray rather than .Formula it won't put in the implicit operator but you will need to put it in a single cell and copy/paste or filldown rather than applying it to the whole range or use another method (like below, I use Column N just for testing).

Sub Jim885()
Dim iRow As Long
iRow = 11
    With Sheets("Sheet1").Range("N2:N" & iRow)
        .Formula = "=SUM(IF($D$2:$D2=D2,($H$2:$H2),0))"
        .FormulaArray = .FormulaR1C1
    End With
End Sub

 

[Jim885]

Thank you all,
I will try each of the suggestions later tonight and report back. I have to test this on the work computer since I don't have 365 at home.
I'm also wondering if SUMIFS can be used instead of either formula.

 

[RoryA]

=SUMIF($D$2:$D2,D2,$H$2:$H2)
and
=IFERROR(SUMIF($D$2:$D2,D2,$H$2:$H2)/E2,0)

 

[Jim885]

fhqwgads,
In my original code, I had;

        Range("M2:M" & iRow).Replace What:="@", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

to handle/remove the @ issue. (same as Ctrl +H). So thanks for thinking about that. I wanted to avoid the @ problem rather than deal with it.

RoryA,
Your approach worked. Thanks! Then I ended up switching some columns around.

Mark858,
Thanks for spending the time to work it out on a spreadsheet and code it as well. I like the switch you added ;

  .FormulaArray = .FormulaR1C1

Very nice.

In the end, I switched some columns around in the spreadsheet and ended up using;

    ws2.Range("K2:K" & iRow).Formula = "=SUMIFS(H$2:H2,$D$2:$D2,D2)"  
    ws2.Range("L2:L" & iRow).Formula = "=E2-K2" 
    ws2.Range("M2:M" & iRow).Formula = "=(SUMIFS($H$2:$H2,$D$2:$D2,$D2)/$E2)"

The code tallies matching values as rows are added to the spreadsheet,

 

[Jim885]

VBE313,
Thanks for the info. All I know is that I got the results I wanted/expected in the original array formula after I removed the "@" from the cells.
The "@" was throwing the original formula result way off.

I hope I never have to deal with the implicit operator. I like life without the @ operator.
Thanks to all.