excel - 根据另一列的值将一列中的值乘以 100，引用表

Question

我有一个表，其中包含字段(列)帐号、名称和余额。对于任何以“100”开头的帐号，相应的余额必须乘以100。

工作表名称为“Data”，表名称为“Data”

我目前正在使用以下代码:

Sub Balance()

Worksheets("DATA").Activate
Dim c As Range

For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    If c.Value Like "100*" Then c.Offset(0, 2).Value = c.Offset(0, 2).Value * 100
Next c

End Sub

这可行，但需要很长时间才能运行，因为它会遍历每一行。我还想要一个不同的代码，它允许我引用列名称而不是使用偏移量，这样如果添加列，我的同事就不必更新代码。

我是宏新手，因此非常感谢您的帮助。

Answer 1

循环遍历一系列单元格本质上很慢。将数据移至变体数组并进行处理，然后将结果移回工作表会更快

试试这个

Sub Balance()
    Dim ws As Worksheet
    Dim rng As Range
    Dim dat1 As Variant, dat2 As Variant
    Dim i As Long

    Set ws = Worksheets("DATA")
    With ws
        Set rng = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    dat1 = rng.Value
    dat2 = rng.Offset(, 2).Value

    For i = 1 To UBound(dat1, 1)
        If dat1(i, 1) Like "100*" Then
            dat2(i, 1) = dat2(i, 1) * 100
        End If
    Next

    rng.Offset(, 2) = dat2
End Sub

利用表的版本

Sub Balance()
    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range
    Dim lo As ListObject
    Dim dat1 As Variant, dat2 As Variant
    Dim i As Long

    Set ws = Worksheets("DATA")
    Set lo = ws.ListObjects("Data")

    Set rng1 = lo.ListColumns("Account Number").DataBodyRange
    Set rng2 = lo.ListColumns("Balance").DataBodyRange
    dat1 = rng1.Value
    dat2 = rng2.Value

    For i = 1 To UBound(dat1, 1)
        If dat1(i, 1) Like "100*" Then
            dat2(i, 1) = dat2(i, 1) * 100
        End If
    Next

    rng2 = dat2
End Sub