动态数组公式，允许目标表扩展-Dynamicarrayformulathatallowsfortargettableexpansion

2  

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 &.

该解决方案的技巧是计算该范围的最后一个单元的地址,并用该地址替换A6。这必须在字符串外部完成,并使用字符串连接运算符&添加到字符串中。

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

但是,Application.Goto,Select和Selection是不必要的。

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编码的方法是:

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):

最后,一个“适当的”VBA解决方案(你明确表示你不想要的那个):

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.

请注意,使用此最后一个解决方案,不再需要手动将数据从Table1预先复制到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:

只需记住在转换它以便在VBA中使用时双引所有单引号。因此得到的VBA看起来像这样:

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.

只需选择表2的A列,然后在单元格A25中输入以下公式。按Ctrl + Enter键将其作为所有选定单元格中的常规公式输入。

=CONCATENATE(A2," - A")

The VBA code for this is:

VBA代码是:

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

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

在您添加或删除Table1中的行时,此公式将自动调整。

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

当然,我建议使用“正确”的全自动VBA代码,不需要手动预复制:

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.

在查看之前的问题和本文提供的数据之后,B-E列的解决方案同样简单。所有这些列都使用相同的公式,使用Ctrl + Enter输入到单元格B25中。

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:

“正确的”全自动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.

请注意,我通过直接使用数字而不是数字作为字符串来简化公式,然后使用VALUE()将它们转换为数字。

0  

You need a range in the formula that dynamically expands according to the text values in column A.

您需要公式中的范围,该范围根据A列中的文本值动态扩展。

=concatenate(A2:index(a:a, match("zzz", a:a)), " - A")

In order to homogenize the numeric entries, they should use the same match formula to determine the terminating row. For column B,

为了使数字条目均匀化,它们应使用相同的匹配公式来确定终止行。对于B栏,

b2:index(b:b, match("zzz", a:a))