Batch activating array formulas

[Timkeller]

Hi,

I have 1200+ .xlsx files from which I have to extract values from column A and B into one 'master workbook'.

All the files are named a certain number; e.g. 1.xlsx, 2.xlsx, … I need to count all values in each of the workbooks that are greater and/or smaller than a certain value in the master workbook.

I thought about solving this using COUNTIFS (for counting with multiple criteria) and INDIRECT (to create a dynamic file path guided by the 'master list' in the master workbook).

However, both functions require that the source workbook be opened, which is cumbersome given the large number of workbooks.

So a solution might have been to use CONCATENATE to create a dynamic filepath for use in a SUMPRODUCT array (see enclosed sample workbook)

(formulas are in Dutch; "TEKST.SAMENVOEGEN" = CONCATENATE, "SOMPRODUCT" = SUMPRODUCT)

Then copy-pasting the formula as 'Values' to the next column.

Now I need to activate all these formulas in column C: going into each cell and pressing CSE one by one works, but this takes a lot of time.

I found the following macro to 'batch' activate the array formula in all these cells:

Please Login or Register  to view this content.

The macro works in the sense that all formulas get the array brackets {}; however this produces a #NAME? error instead of the result of the formula.

However, the formula works perfectly when I manually hit CSE in the cell; so there must be something about this automated way of activating the array formula that produces this error (hope I'm not overlooking something obvious here …).

Would it be possible to make a macro that manually enters CONTROL+SHIFT+ENTER?

I tried entering Application.SendKeys("+^ENTER") into the formula as follows (sorry, no VBA experience):

Please Login or Register  to view this content.

This doesn't seem to work. Anyone any suggestions about how to get this macro working? Any other ideas on a solution for the broader problem are also welcome.

Thanks a lot,
Timothy

[Timkeller]

Sample workbook: DELETED

[Timkeller]

Maybe I should specify my question:

How should I modify the following macro as to use Sendkeys to insert CONTROL + SHIFT + ENTER in each consecutive cell?

Is the statement "+^ENTER" correct for sending the combination of CSE?

Please Login or Register  to view this content.

If I use sendkeys to send the letter "A" it puts "A" several times in the same cell, instead of once in each of the consecutive cells of the selection.

Thanks in advance.

[Timkeller]

For those interested, I managed to force Excel to activate the SUMPRODUCT formulas, by FIND + REPLACE any character in the formula with itself.