vba 将整个范围乘以值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18990541/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiply Entire Range By Value?
提问by user2140261
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:
因此，我能想到的在大范围（大约 450k 行）上完成此操作的最佳方法是使用以下 Sue-do 代码：
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.
这有效，但速度较慢。因为它必须循环每个单元格。
I also tried:
我也试过：
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.
但如果列中有多个值，则会收到运行时错误类型不匹配错误。
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.
我想插入一列并使用"=R-1C * 453.592"Then的公式R1C1 .Value = .Value，然后移动列并覆盖，但看起来很笨重，我认为比粘贴乘法还要慢。
So, does anyone have any better ways of accomplishing this task?
那么，有没有人有更好的方法来完成这项任务？
回答by user2359459
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 ?
有点过时了，但这就是你要找的吗？
回答by ja72
Do not update cell by cell. It is very slow and there is a better way with VBA. Here is the outline:
不要逐个单元更新。它非常慢，VBA 有更好的方法。这是大纲：
- Set a Range to all rows/columns needed to process
- Copy values into array in VBA
- Process the array
- Write the array back into the worksheet in one operation
- 将范围设置为处理所需的所有行/列
- 将值复制到 VBA 中的数组中
- 处理数组
- 一次操作将数组写回工作表
Here is an example:
下面是一个例子：
Public Sub FactorRange(ByRef r_first as Range, ByVal N_rows as Long, _
ByVal N_cols as Long, ByVal factor as Double)
Dim r as Range
'Set range from first cell and size
Set r = f_first.Resize(N_rows,N_cols)
Dim vals() as Variant
' Copy cell values into array
vals = r.Value
Dim i as Long, j as Long
' Do the math
For i=1 to N_rows
For j=1 to N_cols
vals(i,j) = factor * vals(i,j)
Next j
Next i
' Write values back
r.Value = vals
End Sub
回答by user4800441
return_sheet = ActiveSheet.Name
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "CopyPaste"
Selection.Value = 1
Selection.Copy
Sheets(return_sheet).Select 'if necessary select range you whant to multiply
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.DisplayAlerts = False
Sheets("CopyPaste").Delete
Application.DisplayAlerts = True
Sheets(return_sheet).Select
