PDA

View Full Version : Ctrl Shift Enter on FormulaArray VBA



rong3
10-19-2017, 09:20 PM
Hi all , I have a formula Vlookup with multi condition like this:

=VLOOKUP(B3&E3&G3,CHOOSE({1,2},B:B&E:E&G:G,C:C),2,0)
It works if Ctrl Shift Enter on formula to formula array.
So I rewrite it into VBA like

Workbooks(wb_name).Range("C3").FormulaArray="=VLOOKUP(B3&E3&G3,CHOOSE({1,2},B:B&E:E&G:G,C:C),2,0)"
But it only show formula and I must focus on it then Ctrl Shift Enter to display value return.
How to solve this issuse. Please help me.

Bob Phillips
10-20-2017, 02:51 AM
You have to reference the sheet not the workbook, and if you do that works fine for me.

But what is the point of the formula, it will always return what is in C3, as you lookup values are in the lookup ranges.

rong3
10-20-2017, 03:23 AM
You have to reference the sheet not the workbook, and if you do that works fine for me.

But what is the point of the formula, it will always return what is in C3, as you lookup values are in the lookup ranges.
I have just already fixedWorkbooks(wb_name).Sheets(sheet_name).Range("C3").FormulaArray. But it doesn't work.
If i change to Workbooks(wb_name).Sheets(sheet_name).Range("C3").Formula= then the row has value as formula as #N/A, but i need to Ctrl Shift Enter to return exact value.

C3 is default cell value will be return. It is absolutely