更改列中数值的符号(Changing the sign of numeric values in a column)

更改列中数值的符号(Changing the sign of numeric values in a column)

我在D列有数据。

D1列中有一个标题，D2向下有数字值。 我想选择列D中的所有数值（值的数量未知），并将它们乘以-1（替换D列中的当前值）。 我如何通过VBA代码来做到这一点？

如果我可以在Excel中使用公式，我只需将公式D2 * -1向下拖动; 但是，我需要VBA等价物。

I have data in column D.

There is a header in column D1 and numeric values in D2 downward. I would like to select all numeric values in column D (the number of values is unknown) and multiply them by -1 (replacing the current values in column D). How would I do this through VBA code?

If I could use formulas in Excel I would simply drag the formula D2*-1 downward; however, I need the VBA equivalent.

最满意答案

以100,000个随机值进行测试时，几乎可以立即进行以下工作：

Sub MultColDbyOne() Dim i As Long, n As Long, A As Variant n = Cells(Rows.Count, "D").End(xlUp).Row A = Range(Cells(2, "D"), Cells(n, "D")).Value For i = LBound(A) To UBound(A) A(i, 1) = -A(i, 1) Next i Range(Cells(2, "D"), Cells(n, "D")).Value = A End Sub

该子工作首先确定列D中的数据的最后一行，然后将其转移到VBA数组（这有点烦人，只有1列的2维数组），循环遍历该数组替换其中的每个数字其负面，然后将其转回。 这个array然后返回到Range策略在VBA中相当常见（且效率相当高）。

The following works almost instantaneously when tested with 100,000 random values:

Sub MultColDbyOne() Dim i As Long, n As Long, A As Variant n = Cells(Rows.Count, "D").End(xlUp).Row A = Range(Cells(2, "D"), Cells(n, "D")).Value For i = LBound(A) To UBound(A) A(i, 1) = -A(i, 1) Next i Range(Cells(2, "D"), Cells(n, "D")).Value = A End Sub

The sub works by first determining the last row with data in column D, then transferring it to a VBA array (which is, somewhat annoyingly, a 2-dimensional array with only 1 column), looping through that array replacing each number in it by its negative, then transferring it back. This Range to array then back to Range strategy is fairly common (and fairly efficient) in VBA.