INsert right mouse click



Sub EvalutateExample()'Concatenate values in range B2:10 & C2:C10 and'display the result in A2:A10 Dim rng As RangeSet rng = Sheet1.Range("A2:A10") 'Change it as per you requirement 'This is what is generated as a parameter of Evaluate in this procedure'If(Row(1:7),$B$2:$B$10&$C$2:$C$10) rng.Value = Evaluate("If(Row(1:" & rng.Rows.Count & ")," & rng.Offset(, _ 1).Address(, , , True) & " & " & rng.Offset(, 2).Address(, , , True) & ")")EndSub




CTRL V

Sub EvalutateExample() 'Concatenate values in range B2:10 & C2:C10 and 'display the result in A2:A10 Dim rng As Range Set rng = Sheet1.Range("A2:A10") 'Change it as per you requirement 'This is what is generated as a parameter of Evaluate in this procedure 'If(Row(1:7),$B$2:$B$10&$C$2:$C$10) rng.Value = Evaluate("If(Row(1:" & rng.Rows.Count & ")," & rng.Offset(, _ 1).Address(, , , True) & " & " & rng.Offset(, 2).Address(, , , True) & ")")End Sub






Second Main Code. Sub FoxyMultiCellNamedRanges()
Range referencing In Excel and VBA

Code section up to and including Rem 3 take us as far as the last code

Before going onto the rest of the code, as an aside, a review of some basic techniques for bringing a range of data values into a main workbook, such as our "MasturFile.xlsm" , from a closed data workbook, " Data1.xls"

Excel has two basic ways to Hold a single cell reference. Either
it holds it as a fixed co ordinate,
or
it holds it as a fixed vector. The vector corresponds, ( by default settings, if you don’t specify otherwise ), as a fixed angle and direction from the cell to the worksheet origin.
One way in which Excel can be told which system to use by including a $ sign if we want to use the co ordinate system. If no $ is included then Excel holds the fixed vector.
The fixed vector will mean that if a cell reference in a particular cell , such as =B6 , is copied to the next cell to the right, the fixed vector is copied and shifted one place to the right. The fixed vector is responsible for bringing the value from B6 into the original cell. That same vector if placed in the next cell to the right will bring in the value of B7. Excel will then display correspondingly the appropriate reference. This would be either =B7 or $B$7 , but Excel conventionally keeps the convention given to it, so it will display =B7
In VBA things work similarly. If I fill in a single cell with the reference =B6 using the , ( simplified) code line of .._
__ Range(“B2“).Value = “=B6”
____ _.. then the vector is placed in cell B2, so I get

C6.jpg : https://imgur.com/RrR2zrA
Row\Col
A
B
C
D
1
2
=C6
3
4



If I put the same reference, =C6 , across a range, whether manually by
copy / paste, or draging
or
by VBA thus: .. _
__ __ Range(“B2:C3“).Value = “=B6”

____ _.. then the same fixed vector is copied thus:
C6inB2toC3.JPG : https://imgur.com/BrMGrqn

The convention remains to stay in the fixed vector notation, so the reference put in the cells is:
Row\Col
A
B
C
D
E
1
2
 =C6 =D6
3
 =C7 =D7
4
5
6
 a b
7
 c d
8

In the spreadsheet we will see:
Row\Col
A
B
C
D
E
1
2
 a b
3
 c d
4
5
6
 a b
7
 c d
8