将范围中的每个值乘以常量，但跳过空白单元格

I need a simple a fast solution for multiplying all values in a range by a numeric value in VBA code. I know about this solution: Multiply Entire Range By Value?

我需要一个简单的快速解决方案，用于将范围内的所有值乘以VBA代码中的数值。我知道这个解决方案：按价值乘以整个范围？

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

But it has a big drawback - if the original cell was blank, it results in zero. How to force it skip blank values?

但它有一个很大的缺点 - 如果原始单元格为空白，则会导致零。如何强制它跳过空白值？

I want to avoid looping through the values because it is very slow.

我想避免循环遍历值，因为它非常慢。

3 个解决方案

#1

You can use your existing approach with Evaluate but get a little smarter with it - it can take conditions etc, so just include a test for ISBLANK. This example is tested on a combination of blank and non-blank cells in the range A1:C3 - just update for your range and give it a try:

您可以将现有方法与Evaluate一起使用，但可以更聪明地使用它 - 它可以采取条件等，因此只需包含ISBLANK的测试。此示例在A1：C3范围内的空白和非空白单元格的组合上进行测试 - 只需更新您的范围并尝试一下：

Option Explicit

Sub Test()

    Dim rng As Range

    Set rng = Sheet1.Range("A1:C3")

    'give the name a range so we can refer to it in evaluate
    rng.Name = "foo"

    'using Evaluate
    rng = Evaluate("IF(ISBLANK(foo),"""",foo*2)")

    'using [] notation
    'preferred IMO as dont need to escape "
    rng = [IF(ISBLANK(foo),"",foo*2)]

End Sub

#2

I know you have an accepted answer, but for whatever it's worth it turns out you don't have to name the range. And in case the cells in the range contain text, then this one-line code works fine

我知道你有一个接受的答案，但无论它值多少，事实证明你不必命名范围。如果范围中的单元格包含文本，那么这个单行代码可以正常工作

Sub MultiplyRangeByConstant()
    [A1:C3] = [IF(ISBLANK(A1:C3),"",IF(ISTEXT(A1:C3),A1:C3,2*A1:C3))]
End Sub

#3

if there are formulas or anything else in the range:

如果范围内有公式或其他任何内容：

'[a1:b3] = [{"=1","a";2,"=0/0";"",3}]
[a1:b3] = [if(a1:b3="","",if(isNumber(a1:b3),a1:b3*2,a1:b3))]

or to ignore the formulas, the good old PasteSpecial

或忽略公式，好旧的PasteSpecial

Set temp = [c1].EntireRow.Find("") ' any blank cell that is not in the range
temp.Value = 2
temp.Copy
[a1:b3].SpecialCells(xlCellTypeConstants).PasteSpecial , Operation:=xlMultiply
temp.Value = ""

#1

Option Explicit

Sub Test()

    Dim rng As Range

    Set rng = Sheet1.Range("A1:C3")

    'give the name a range so we can refer to it in evaluate
    rng.Name = "foo"

    'using Evaluate
    rng = Evaluate("IF(ISBLANK(foo),"""",foo*2)")

    'using [] notation
    'preferred IMO as dont need to escape "
    rng = [IF(ISBLANK(foo),"",foo*2)]

End Sub

#2