1

I'm stumped and hoping for an answer to this weird issue.

In my VBA code, I'm creating a formula that access a column in my table called "Agent". (tbl_CSV_Import_Values[Agent]). The Agent column is in column 8 on my sheet. The table gets created/removed sometimes, and can have a different number of rows. After deleting the table, I recreate it, then run the VBA code to rewrite the formulas. For other formulas this works fine. But...

When I use the below code to stuff the formula into cell F3, it adds "@" to TWO of the "Agent" column References. For no reason I can discern. But it weirdly leaves one of the 3 Agent references alone. (See below)

My VBA:

  ActiveSheet.Range("F3").Formula = "= ""Number of Agents:   "" & SUM(IF(tbl_CSV_Import_Values[Agent]<>"""",1/COUNTIF(tbl_CSV_Import_Values[Agent], tbl_CSV_Import_Values[Agent]), 0))"

The Formula that get's place in cell F3 (generating an error due to those "@"s in front of Agent:

= "Number of Agents:   " & SUM(IF(tbl_CSV_Import_Values[@Agent]<>"",1/COUNTIF(tbl_CSV_Import_Values[Agent], tbl_CSV_Import_Values[@Agent]), 0))

Of course, the formula fails... unless I simply delete those "@" signs from the formula manually. (The excel error says "A value used in the formula is a wrong data type".) With the two @'s deleted, the formula immediately works fine.

Does anyone have any idea why Excel is deciding to whack my column references (and only 2 of the 3?!) like that, and what I can do to stop it?

-Daniel

Daniel Barrett
  • 21
  • 4
  • Try changing `.Formula` to `.Formula2`. – BigBen Mar 30 '21 at 01:48
  • The disadvantage of structured references is that there is no $ sign to make references absolute. Instead there is the @ sign, for much the same purposes. But since structured references are supposed to be easier to use than legacy ones you don't get to choose. The effect is that, if the structured reference doesn't do what you want, you shouldn't use it. – Variatus Mar 30 '21 at 02:17
  • [This](https://stackoverflow.com/q/61138029/9758194) should be helpfull. Let me know if it does, so we can mark this as a duplicate. – JvdV Mar 30 '21 at 07:20
  • 1
    Yes JvdV it certainly does! I'm so sorry I didn't run across that in my searches--what a great answer is was! Thank you so much! (And Formula2 did just what I needed--I learned something today!) – Daniel Barrett Mar 30 '21 at 13:20
  • @DanielBarrett, that's completely fine. For some reason it's very hard to search for the "@" in addition to good search terms. It won't just show easily. – JvdV Mar 31 '21 at 20:47

1 Answers1

1

If the first parameter in IF (IF(tbl_CSV_Import_Values[Agent]<>"""",...) and the second parameter in COUNTIF (COUNTIF(..., tbl_CSV_Import_Values[Agent])) shall be a reference to a range of cells rather than a single cell, then the formula would must be an array formula. So in former Excel versions the formula would must be applied as an array formula by pressing Ctrl+Shift+Enter.

Since Excel 365 the decision whether array formula or not is tried to choose automatically. But when set via VBA using Range.Formula, it always sets a normal formula and not an array formula. And in a normal formula first parameter in IF and second parameter in COUNTIF cannot be column references but must be references to single cells. That's why the adding of the @.

To avoid this do explicitly set an array formula using VBA:

ActiveSheet.Range("F3").FormulaArray = "= ""Number of Agents:   "" & SUM(IF(tbl_CSV_Import_Values[Agent]<>"""",1/COUNTIF(tbl_CSV_Import_Values[Agent], tbl_CSV_Import_Values[Agent]), 0))"
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • 1
    Hello Axel, with Excel365 came a new standard for dynamic array formulas. See [this](https://stackoverflow.com/a/61138478/9758194) answer I wrote a while back. – JvdV Mar 30 '21 at 07:23
  • @JvdV: Yes, I know. But this is partially another problem as here no new dynamic array function is used. Maybe using `Range.Formula2` will help here too. But using `Range.FormulaArray` will be more backwards compatible as `Range.Formula2` cannot be used in `Excel 2016` and lower. – Axel Richter Mar 30 '21 at 07:33
  • Alright, in case of backward compatibility I understand =). – JvdV Mar 30 '21 at 07:51