Help prevent "@" from being added to VBA written OFFSET formulas

[braindiesel]

I have code writing a complex function... what the formula does is irrelevant...
ActiveCell.Formula = "=HOUR(SMALL((OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),1,COUNTIF($A$3:$A$" & myBot & ",F3),1))*(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),2,COUNTIF($A$3:$A$" & myBot & ",F3),1)=1),ROWS($G$3:G3)+COUNTBLANK(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),2,COUNTIF($A$3:$A$" & myBot & ",F3),1))))"

In the cell, the macro writes what I want but adds in two @ characters
=HOUR(SMALL((@OFFSET($A$2,MATCH(F3,$A$3:$A$12,0),1,COUNTIF($A$3:$A$12,F3),1))*(@OFFSET($A$2,MATCH(F3,$A$3:$A$12,0),2,COUNTIF($A$3:$A$12,F3),1)=1),ROWS($G$3:G3)+COUNTBLANK(OFFSET($A$2,MATCH(F3,$A$3:$A$12,0),2,COUNTIF($A$3:$A$12,F3),1))))

It results in a NUM! error.
When I remove the 2 added @ symbols, the formula works perfectly

How do I prevent Excel from breaking my formula

 

[6StringJazzer]

When I run your exact same code I do not get the "@" sign inserted. It works perfectly. I have never seen anything like that happen.

Is there any possibility of sharing your file?

 

[braindiesel]

This is basically it... it is just a dummy sheet to help solve another persons question on this site and I ran into this @ nonsense.

Below s entire sheet and entire code... a work in progress

Location	Time	7/1/2015​	6/1/2015​
AAA	10:10​	1​			AAA	#NUM!​	AAA	0.423611​	AAA	1​	AAA
AAA	10:16​		1​		AAA	#NUM!​		0.427778​	AAA	1​
AAA	13:53​	1​			AAA	#NUM!​		0.578472​	AAA	1​
AAA	15:37​	1​			AAA	#NUM!​		0.650694​	AAA	1​
AAA	16:25​		1​		AAA	#NUM!​		0.684028​	AAA	1​
AAA	17:35​	1​			AAA	10​		0.732639​	BBB	0​
AAA	21:35​		1​		AAA	16​		0.899306​	BBB	0​
AAA	21:44​	1​			AAA	21​		0.905556​	BBB
BBB	14:03​		1​			#N/A​			BBB
BBB	14:13​	1​				#N/A​			AAA

Sub summarizeIt()
    Range("A3").Select 'change F3 to your starting cell
    
    myBot = Range("A2").End(xlDown).Row
    myTarg = Range("A3").Value
    myNowCtr = Application.SumIf(Range("A3:C" & myBot), myTarg, Range("C3:C" & myBot))
    myThenCtr = Application.SumIf(Range("A3:C" & myBot), myTarg, Range("D3:D" & myBot))
    
    Range("F3").Resize(myNowCtr + myThenCtr, 1).Value = myTarg
    Range("G3").Select
    ActiveCell.Formula = "=HOUR(SMALL((OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),1,COUNTIF($A$3:$A$" & myBot & ",F3),1))*(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),2,COUNTIF($A$3:$A$" & myBot & ",F3),1)=1),ROWS($G$3:G3)+COUNTBLANK(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),2,COUNTIF($A$3:$A$" & myBot & ",F3),1))))"
        '=HOUR(SMALL((OFFSET(R2C1,MATCH(RC[-1],R3C1:R12C1,0),1,COUNTIF(R3C1:R12C1,RC[-1]),1))*(OFFSET(R2C1,MATCH(RC[-1],R3C1:R12C1,0),2,COUNTIF(R3C1:R12C1,RC[-1]),1)=1),ROWS(R3C7:RC)+COUNTBLANK(OFFSET(R2C1,MATCH(RC[-1],R3C1:R12C1,0),2,COUNTIF(R3C1:R12C1,RC[-1]),1))))"
    'HOUR(SMALL((OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),1,COUNTIF($A$3:$A$" & myBot & ",F3),1))*(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),2,COUNTIF($A$3:$A$" & myBot & ",F3),1)=1),ROWS($G$3:G3)+COUNTBLANK(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),2,COUNTIF($A$3:$A$" & myBot & ",F3),1))))
    Selection.Resize(myNowCtr, 1).Select
    Selection.FillDown
    ActiveCell.Offset(myNowCtr, 0).Select
    ActiveCell.Formula = "=HOUR(SMALL((OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),1,COUNTIF($A$3:$A$" & myBot & ",F3),1))*(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),3,COUNTIF($A$3:$A$" & myBot & ",F3),1)=1),ROWS($G$3:G3)+COUNTBLANK(OFFSET($A$2,MATCH(F3,$A$3:$A$" & myBot & ",0),3,COUNTIF($A$3:$A$" & myBot & ",F3),1))))"
    Selection.Resize(myThenCtr, 1).Select
    Selection.FillDown
    
End Sub

 

[Fluff]

Use Formula2 not Formula

 

[MARK858]

@braindiesel see the previous post by Fluff (post number 4)

It works perfectly. I have never seen anything like that happen.

Hi 6StringJazzer, just for info...

You won't get the issue as you aren't using 365.

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

 

[Fluff]

One caveat to post#4, I think that formula2 only works if you have dynamic arrays.

 

[braindiesel]

Use Formula2 not Formula

Seriously? That was it... smdh...
Thank you for clearing up faster than it took me to grab an aspirin.

 

[6StringJazzer]

You won't get the issue as you aren't using 365.

I have access to 365, I will have to install it. I was not familiar with this feature. It seems that Microsoft is adding a bunch of marginally useful features in 365 to herd everybody into its subscription model.

The OP does not have his version in his profile so this did not occur to me. Thanks.

 

[braindiesel]

The OP does not have his version in his profile so this did not occur to me. Thanks.

Never occurred to me to do that.
I teach Microsoft Office. I have all of the versions but living in 365 now

 

[Fluff]

You can update your account details to show which version of Excel you are using.