VBA Formula that require Control+Shift+Enter

**RxMiller** · 05-26-2009, 02:11 PM

VBA automation -
Excel added the formula - but they all return "Not Found"
After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar.

In VBA I tried to use the:
objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The Excel Formula bar for the cell is just empty (blank)

Using only the Formula property:
objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....

The correct formula is created in each cell, but...
Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets.

In Excel - my vba code successfully constructs these formulas:
In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.

Code:

=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(73945&"@"&-10000,( PositionDataSell!$D$2:$D$505)&"@"&(PositionDataSell!$S$2:$S$505),0))),"Not Found", INDEX(PositionDataSell!$T$2:$T$505,MATCH(73945&"@"&-10000,( PositionDataSell!$D$2:$D$505) & "@" & (PositionDataSell!$S$2:$S$505),0)))

and, maybe I also need to think of a way to prevent the users from visiting a cell as well
Maybe I hide the cells with the actual formula and display a cell with the actual value of the cell with a formula?

**RxMiller** · 05-26-2009, 02:26 PM

Thought I might use the Macro Recorder to hight light a cell with the formula and click Control+Shift+ enter
A message that the recorder can't deal with this action appears.
Sure enough, it just skipped over the action.
Tried to add the { bracket around the code in the VBA code. Nope, that does not work either.

**DonkeyOte** · 05-26-2009, 02:37 PM

I'm only dropping in briefly but bear in mind that a VBA Formula Array can not exceed 255 chars in length.

**RxMiller** · 05-26-2009, 03:29 PM

OMG!
You know, I have this very complex report that uses VBA to program around 3,000 cells formulas, dynamic subtotals and groupings (all different every day).

Now, after all that great programming my logic fell short here:
VBA (From Access this time) put a formula into the cell. If I go to the cell and use Control+Shift+Enter, the formula received the curly brackets and worked perfectlly.
Therefore: the string in the formula could not possibly be over 255 characters.

Bad Logic on my part.
My string happened to be 261 characters. (That is over 255 for sure.)
So, I used search and replace to shorten my very verbose tab name that was used many times in this formula. Now my formula (per VBA point of view) is 241 characters.

And it all works as expeced.
That will teach me to "ASSUME".

And it is not like you didn't mention this to me last week.
My excuse is: It was a three day weekend - I probably got too much sun while cross-country skiing in Vail. The good wine had absolutely nothing to do with it.

At the risk of being laughed at, I will post this so others can recgonize the awkward signs of the cell just being blank when using the FormulaArray method since there is no error code or error trap for this issue.

**hoffey** · 08-19-2009, 01:01 PM

Any way to shorten my formula? It works when I do it manually in Excel, but not when I use VBA.

Code:

=IF($AA$1, IF(ISNA(VLOOKUP(LEFT($C4,7)+0,PKEY,2,FALSE)),IF(ISNA(VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)), "UNDEFINED",VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)),(INDEX(PKEY,SMALL(IF(OFFSET(PKEY,0,0,ROWS(PKEY),1)=(LEFT(C4, 7)+0), ROW(OFFSET(PKEY,0,0,ROWS(PKEY),1))-ROW( OFFSET(PKEY,0,0,1,1) )+1, ROW(OFFSET(PKEY,ROWS(PKEY)-1,0,1,1))+1),COUNTIF(OFFSET(PKEY,0,0,ROWS(PKEY),1),(LEFT(C4, 7)+0))),2))), IF(ISNA(VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)), "UNDEFINED", VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)))

and I'm using this VBA:

Code:

    With Worksheets("Revenue")
        Set my_range = .Range("B4:B" & .Range("C65536").End(xlUp).Row)
        my_range.FormulaArray = "=IF($I$1, IF(ISNA(VLOOKUP(LEFT($C4,7)+0,PKEY,2,FALSE)), _
IF(ISNA(VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)), ""UNDEFINED"",VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)), _
(INDEX(PKEY,SMALL(IF(OFFSET(PKEY,0,0,ROWS(PKEY),1)=(LEFT(C4, 7)+0), _ 
ROW(OFFSET(PKEY,0,0,ROWS(PKEY),1))-ROW( OFFSET(PKEY,0,0,1,1) )+1, _ 
ROW(OFFSET(PKEY,ROWS(PKEY)-1,0,1,1))+1),COUNTIF(OFFSET(PKEY,0,0,ROWS(PKEY),1), _
(LEFT(C4, 7)+0))),2))), IF(ISNA(VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)), ""UNDEFINED"", _
VLOOKUP(LEFT($C4,4)+0,PKEY,2,FALSE)))"
    End With

Thanks in advance, not sure what to do here. :/