Excel modifies my vba generated formula strings and causes errors (using @)

Hi all.
I’m constructing a table where the user can define ranges of interest, and get quick overview of any errors in critical areas of the project.

In order to avoid using a number of INDIRECT() calls, I thought I would simply have vba copy the range strings and generate the required formulas.
However these are multi cell ranges, so to avoid #SPILL errors, I had to include an @ to enable implicit intersection.

However when excel sets the formula string, it inserts an additional “@” to make them compatible with older versions, and this causes the formulas to show incorrect results.
IE the expected

Formula:

Please Login or Register  to view this content.

becomes

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

becomes

Formula:

Please Login or Register  to view this content.

How do I prevent excel from changing my formula strings?

Here is my code, and an example work book is attached.

Please Login or Register  to view this content.

Please change .Formula to .Formula2

Please Login or Register  to view this content.

Thanks.

But this is now showing me that my approach was wrong. It's entering the formulas correctly, but the formulas now ignore anything but the first cell in the range (or one that match the row/column of the formula). I now see that this is a property of Implicit Intersection. Any suggestions?

No idea what you try to do.
Maybe try

Please Login or Register  to view this content.

Originally Posted by Bo_Ry

No idea what you try to do.

I'm trying to look at a range, and have the formula return details about any errors that occurred.
Column "D" should return true if there are any errors in the range.
Column "E" attempts to return to the Type of the error, but this is optional
Column "F" is just a text lookup based on column "E", also optional.

Originally Posted by Bo_Ry

Maybe try

Please Login or Register  to view this content.

The problem here is that by not inserting an @, and using .formula, the "@" is automatically added at the range statements, meaning that the ranges are treated as a single cell.
The formula in "D" returns true when there is no error, and False when there is an error, the opposite of expected behavior.


If I try the same with .formula2, I get spill errors from the Dynamic Arrays calc.

And if I insert the intersect operator myself, and use .formula2 (as you suggested before) it also doesn't work.


I don't see a way forward at the moment.

Maybe try

Please Login or Register  to view this content.

Thanks Bo_Ry. This works perfectly.

I'm not sure I understand though.
Why is the lookup value a '9' in

Please Login or Register  to view this content.

?

Thanks again

9 can be any large number that more than the number from ERROR.TYPE

9 can lookup for 0-9
20 can lookup for 0-20