根据另一列的值引用表格，将某一列中的值乘以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

这个方法可以运行，但是需要很长时间，因为它会遍历每一行。我还想有一个不同的代码，可以让我引用列名而不是使用偏移量，这样我的同事就不必在添加列时更新代码了。

我对宏不熟悉，所以非常感谢您的帮助。

- CKIM

---

出于好奇（因为我认为克里斯·尼尔森的答案可能是最好的解决方案），您的电子表格中是否有很多引用余额的公式？并且计算设置为自动吗？如果是这样，每次更改一个余额值时，您可能会重新计算许多其他单元格。因此，在代码开始时仅以编程方式将计算设置为手动，然后在结束时将其设置回自动，您可以获得大量的速度提升。 - YowE3K

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

利用 Table 的版本

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

- chris neilsen

---

这太完美了！速度快得离谱。能否用表格引用替换这些引用？ - CKIM

这正是我所需要的。谢谢你的帮助。 - CKIM

@CKIM，由于您是新来的，请在获得满意答案后通过点击勾选（完全取决于您接受哪个答案，如果有的话）来“接受”它。 - chris neilsen

Answer 2

使用Range.Find和Range.FindNext来识别匹配的单元格，而不是循环遍历单元格或数组。

Sub Balance()

Worksheets("Sheet1").Activate
Dim c As Range
Dim AccountCol As Long
Dim BalanceCol As Long
Dim LastAccountRow As Range
Dim FirstFoundCell As Range

    AccountCol = WorksheetFunction.Match("Account", Rows("1:1"), 0)
    BalanceCol = WorksheetFunction.Match("Balance", Rows("1:1"), 0)

    With Range(Cells(1, AccountCol), Cells(Cells(Rows.Count, 1).End(xlUp).Row, AccountCol))
        Set c = .Find("100", .Cells(1, AccountCol), xlValues, xlPart)
        If Not c Is Nothing Then
            Set FirstFoundCell = c
            If Left(c.Value2, 3) = "100" Then
                Cells(c.Row, BalanceCol) = Cells(c.Row, BalanceCol) * 100
            End If
            Set c = .FindNext(c)
            Do Until c.Address = FirstFoundCell.Address
                If Left(c.Value2, 3) = "100" Then
                    Cells(c.Row, BalanceCol) = Cells(c.Row, BalanceCol) * 100
                End If
                Set c = .FindNext(c)
                If Left(c.Value2, 3) = "100" Then
                    Cells(c.Row, BalanceCol) = Cells(c.Row, BalanceCol) * 100
                End If
            Loop
        End If
    End With
End Sub

这会在“账户”列中查找第一个包含“100”的单元格，确认找到的值以100开头，并将余额乘以100。然后尝试查找下一个包含“100”的单元格，直到找到的单元格与第一个找到的单元格匹配。

要引用表格，只需用结构化引用替换“Match”函数来引用表格列：

    AccountCol = [Table1[Account]].Column
    BalanceCol = [Table1[Balance]].Column

- Mark Fitzgerald

Answer 3

对于这个问题，你根本不需要使用循环 - range或数组。

这段代码是VBA单次范围等效的代码：IF(LEFT(A2,3)="100", A2*100,A2)

代码

Sub Short()
 Dim rng1 As Range
 With Sheets("data")
     Set rng1 = .Range(.[a2], .Cells(Rows.Count, "A").End(xlUp))
     rng1.Value2 = Evaluate("=IF(LEFT(" & rng1.Address & ",3)=""100""," & rng1.Address & "*100," & rng1.Address & ")")
 End With
End Sub

- brettdj

Answer 4

这可能会更快一些（没有测试，但与@brettdj的答案非常相似）：

[Data[Balance]] = [IF(LEFT(Data[Account Number],3)="100", Data[Balance]*100, Data[Balance])]

- Slai