通过价值乘以整个范围？ [英] Multiply Entire Range By Value?

问题描述

所以我可以想到在大范围（约450k行）完成这个最好的方法是使用以下Sue-do代码：

So The best way I could think of to accomplish this over a large range (about 450k rows) was to use the following Sue-do code:

Range("A1").Copy ' A1 Contains Value I want to multiply column by
Range("MyTable[FooColumn]").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

现在这有效，但是我必须复制和粘贴该值似乎是多余的，因为值是永远不会改变。

Now this works, but the fact that I have to copy and paste that value seems redundant as the value is never going to change.

For Each c In Range("MyTable[MyColumnHeader]")
    If IsNumeric(c) And Not c = "" Then
        c.Value = c.Value * 453.592 ' The value that is in A1 from previos sample
    End If
Next

这有效，但速度较慢。因为它必须循环每个单元格。

That works, but is slower. As it has to loop every cell.

我也试过：

With Range("MyTable[MyColumnHeader]")
    .Value = .Value * 453.592
End With

但收到运行时错误类型不匹配错误，如果列中有多个值。

But received runtime error Type Mismatch Error if there was more then one value in the column.

我考虑过插入一列并使用公式R1C1的= R-1C * 453.592然后 .Value = .Value ，然后移动列并覆盖，但似乎笨重，我也会认为也比较慢，然后粘贴乘法。

I thought about inserting a column and using the formulaR1C1 of "=R-1C * 453.592" Then .Value = .Value, Then shift the column and overwrite but seemed clunky and I would think also slower then the paste multiply.

那么，有没有人有任何更好的方法来完成这项任务？

So, does anyone have any better ways of accomplishing this task?

推荐答案

Sub Test()

    Dim rngData As Range

    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
    rngData = Evaluate(rngData.Address & "*2")
End Sub

过时的类型，但是你在寻找什么？

Kind of outdated but is that what you were looking for ?

这篇关于通过价值乘以整个范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！