Controlling Shifted references before Entering.

Briefly, how Excel works:
Excel updates a worksheet cell for cell , and Interception theory ( http://www.excelfox.com/forum/showth...on-and-VLookUp ) explains how a controlled synchronisation allows any single reference to intercept with the help matrix of effectively a full spreadsheet full with the value at the referenced cell. In the first instance this prevents the pasting of a multi cell range into a worksheet. The technique discussed in the last post overcomes the problem in that the pasting of a fixed vector effectively keeps the synch , or effectively shifts the reference exactly in step with the cell by cell up date. Similarly, a manual or code copy and paste of a range will work as the copy will be in synch with the paste. The problem comes if we try to directly write in a multicell reference, such as = B11:C12, either manually or by a code such as
Range("A1:B2").Value = "=B11:C12"
If we are forced into this simultaneous pasting of a range we can use a technique whereby, before the next update each reference in the multicell range to be pasted is effectively shifted by its offset / vector. This is a sort of Controlled Shifting before the Enter. Excel allows us to call in this process if we use the keys Ctrl+Shift+Enter in place of the Enter. The effect of this process is to have an array of values to update in the same time, or rather in the same synch period ( delayed / increased in time by N times, where N is the number of elements in the range ) due to the shifting , that a single cell would have been updated. We may define an area of the worksheet to accommodate this returned array of values.

Manually the complete process involves the following
_ The area for the returned array will be taken as the current selection, so your wanted area for display should be selected.
_ Hit F2 or click in the Formula Bar
_ Type in the formula for the range reference of the range you want to use ( like =B11:C12 )
_ Hit Ctrl+Shift+Enter
The above is sometimes referred to as Type 2 CSE.

In Excel, the indication that a spreadsheet area is being used in this way can be seen by the presence of curly brackets: In the formula bar, one can see if a range has a formula entered by the CSE method as it has curly brackets on it : _ {=B11:C12}
http://www.excelfox.com/forum/showth...alue#post10038


In Excel VBA, A range object that is used to hold an array will have the indication of that by virtue of it having a .FormulaArray Property.
As we saw in previous posts, we can make reference via a range object to its .Value property and therefore assign it, as we did thus:
Range(" ").Value = "=B6"
That is equivalent to writing =B6 in a cell.

By doing something similar with the .FormulaArray property we effectively do the same as in the complete manual process for CSE as described above,
A code line such as .._
__ Range("myRange").FormulaArray = "=B11:C12"
____ _.. will effectively define the myRange area in the spreadsheet as that area for the returned array of values from the range B11:C12
In other words, if, as example, I want the 4 values of the range B11:C12 at the top left of a worksheet, then this code line will do that for me:
Range("A1:B2").FormulaArray = "=B11:C12"
( The manual equivalent of doing that is
_ Select the range to be used for the retuned array of values ( A1:B2 )
_ Hit F2 or click in the Formula Bar
_ Type in _ =B11:C12
_ Hit Ctrl+Shift+Enter )

We can now see how to bring an array of data values from our data worksheets into the master file with code lines of this form
Range("B11:C12").FormulaArray = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12"
Application.Range("B11:C12").FormulaArray = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12"
Application.Range("='C:\MyFolder\[MasturFile.xlsm]Tabelle1'B11:C12").FormulaArray = "='C:\MyFolder\[Data2.xlsx]Tabelle1'!B11:C12"

In those code lines we can replace the range references in those formulas, with named ranges in exactly the same way that we have done previously once we have created the named ranges to refer to those two ranges
The final code lines will be very similar to the previous, differing mainly in the use of .FormulaArray instead of .Value
( Note: In the code lines previously for single cell ranges, the .Value could also have been replace with .FormulaArray : We would effectively have been pasting in an array of one element, which is perfectly valid. )

_.____

From Rem 4 in the second demo code, ( Sub FoxyMultiCellNamedRanges() ) we begin to incorporate named ranges into code lined to bring in the data from the two data files into the main file……

_....................


Ref CSE
https://usefulgyaan.wordpress.com/20...1/#comment-737
http://www.excelfox.com/forum/showth...alue#post10038