将范围内的每个值乘以常数，但跳过空白单元格 [英] Multiply each value in a range by a constant, but skip blank cells

问题描述

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

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?

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.

推荐答案

您可以使用现有的方法与评估，但是有一点更智能 - 它可以采取条件等，所以只包括 ISBLANK 的测试。此示例在范围 A1：C3 中的空白单元格和非空白单元格的组合之间进行测试 - 只需更新您的范围并尝试：

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:

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

这篇关于将范围内的每个值乘以常数，但跳过空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！