braindiesel
Well-known Member
- Joined
- Mar 16, 2009
- Messages
- 571
- Office Version
- 365
- 2019
- 2010
- Platform
- Windows
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
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