Dynamic array formula that allows for target table expansion

Question

I have a worksheet where I copy data from Table1 to Table2.

Once I have copy-pasted the data to Table2, which sits below Table1, I select the first cell in the first column of Table2 and press Ctrl+Shift+Down to select down to the last used cell. Finally, I apply a concatenation array formula that adds a suffix to the values of the related cells in Table1. I have recorded these Table2 steps as a macro.

To demonstrate, Table1 looks like this:

Table1

   |  A      B   C   D   E
---+-----------------------
 1 | Name    V1  V3  V3  V4
---+-----------------------
 2 | Wood    10  10  10  10
 3 | wood    28  28  28  28
 4 | tree    30  45  60  68
 5 | plastic 50  50  50  50
 6 | tree    50  50  50  50
 7 | iron    64  75  75  80

This is the formula I use in column A of Table2:

{=concatenate(A2:A7," - A")}

This is the result after applying it to Table2:

Table2

   |  A               B    C    D    E
---+-----------------------------------
25 | Wood - A        25   25   25   25
26 | wood - A        50   50   50   50
27 | tree - A        50   50   100  100
28 | plastic - A     100  100  100  100
29 | tree - A        100  100  100  100
30 | iron - A        100  100  100  100

Now, when I add new entries to Table1, for example in cells A8 & A9:

Table1a

   |  A      B   C   D   E
---+-----------------------
 1 | Name    V1  V3  V3  V4
---+-----------------------
 2 | Wood    10  10  10  10
 3 | wood    28  28  28  28
 4 | tree    30  45  60  68
 5 | plastic 50  50  50  50
 6 | tree    50  50  50  50
 7 | iron    64  75  75  80
 8 | table   20  25  0   30
 9 | plastic 54  35  21  0

after running the recorded macro, instead of using the new range A2:A9, it's using the previous recorded range (A2:A7), resulting in the #N/A errors as can be seen in Table2a:

Table2a

   |  A               B    C    D    E
---+-----------------------------------
25 | Wood - A        25   25   25   25
26 | wood - A        50   50   50   50
27 | tree - A        50   50   100  100
28 | plastic - A     100  100  100  100
29 | tree - A        100  100  100  100
30 | iron - A        100  100  100  100
31 | #N/A            #N/A #N/A #N/A #N/A
32 | #N/A            #N/A #N/A #N/A #N/A

This is because in the array formula, the range is not dynamic, but fixed.

So, I would like a formula which auto-adjusts the range when new entries are added or deleted from Table1. Something similar to what I have in the recorded macro which selects all the cells in column A of Table2, before applying the array formula:

Application.Goto Reference:="R25C1"
Range("A25", Range("A25").End(xlDown)).Select

I was thinking of something like the following, where I provide the starting cell of Table1 and the range down to the last cell with data is worked out automatically:

Selection.FormulaArray = "=concatenate(Range("A2",Range("A2").End(xldown)).Select,""- A"")"

I would like a formula solution, that I can apply to all the cells. I don't want to define variables, etc, to do this.

robinCTS robinCTS · Accepted Answer · 2018-03-10T04:49:28

The answer is as simple as:

Selection.FormulaArray = "=concatenate(A2:" & Range("A2").End(xlDown).Address & ","" - A"")"

Note that you have to convert every single quote, ", in the original array formula to double quotes, "".

The trick with the solution is to calculate the address of the last cell of the range, and replace the A6 with this address. This has to be done outside the string, and added to the string by using the string concatenation operator &.

However, the Application.Goto, Select, and Selection are unnecessary.

Thus you really should use:

Range("A25", Range("A25").End(xlDown)).FormulaArray _
= "=concatenate(A2:" & Range("A2").End(xlDown).Address & ","" - A"")"

Another way of coding this, using With, would be:

With Range("A25", Range("A25").End(xlDown))
  .FormulaArray = "=concatenate(A2:" & Range("A2").End(xlDown).Address & ","" - A"")"
End With

Finally, a "proper" VBA solution (the one you specifically said you didn't want):

With Range("A2").End(xlDown)
  Range("A25", .Offset(25 - 2)).FormulaArray = "=concatenate(A2:" & .Address & ","" - A"")"
End With

Note that with this last solution, there is no longer a requirement to manually pre-copy the data from Table1 to Table2.

ADDENDUM:

Just realised that you were asking for a "pure" formula solution. If that's all you're after, then this formula works:

{=CONCATENATE(A2:INDEX(A1:A24,MATCH("*",A1:A24,-1))," - A")}

Just remember to double quote all the single quotes when converting it for use in VBA. The resulting VBA would thus look like this:

Selection.FormulaArray = "=CONCATENATE(A2:INDEX(A1:A24,MATCH(""*"",A1:A24,-1)),"" - A"")"

X-Solution _{^{(See What is the XY problem?)}}

Also just realised that you don't actually need a complicated dynamic formula if you just use a normal formula instead of an array formula.

Just select column A of Table2 and enter the following formula in cell A25. Press Ctrl+Enter to enter it as normal formula in all the selected cells.

=CONCATENATE(A2," - A")

The VBA code for this is:

Selection.Formula = "=CONCATENATE(A2,"" - A"")"

This formula will automatically adjust as you add or remove rows from Table1.

Of course, I would recommend using the "proper" fully automatic VBA code which doesn't require manual pre-copying:

Range("A25", Range("A2").End(xlDown).Offset(25 - 2)).Formula = "=CONCATENATE(A2,"" - A"")"

And Finally:

After looking at your previous questions and the supplied data in this one, the solution for columns B-E is similarly simple. The same formula is used for all those columns, entered into cell B25 using Ctrl+Enter.

Normal formula:

=IF(B2<25,25,IF(B2<50,50,100))

Equivalent VBA:

Selection.Formula = "=IF(B2<25,25,IF(B2<50,50,100))"

"Proper" fully-auto VBA:

With Range("A2").End(xlDown)
  Range("B25", .Offset(25 - 2, 4)).Formula = "=IF(B2<25,25,IF(B2<50,50,100))"
End With

Note that I have simplified the formula by using numbers directly instead of numbers as strings and then using VALUE() to convert them to numbers.

Dynamic array formula that allows for target table expansion

2 Answers

X-Solution (See What is the XY problem?)

X-Solution _{^{(See What is the XY problem?)}}