将列中的值乘以100，基于另一列的值，引用表

I have a table that has the fields (columns) Account Number, Name, and Balance. For any account numbers beginning with "100", the corresponding balance must be multiplied by 100.

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

The sheet name is "Data", the table name is "Data"

工作表名称为“数据”，表名为“数据”

I am currently using the following code:

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

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

This works, but it takes a long time to run since it goes through every line. I also want a different code that would allow me to reference the column name instead of using offset, so my colleagues wouldnt have to update the code if columns are added.

这是有效的，但它需要很长时间才能运行，因为它遍历每一行。我还想要一个不同的代码，允许我引用列名而不是使用offset，所以如果添加了列，我的同事就不必更新代码。

I'm new to macros, so help would be greatly appreciated.

我是宏的新手，所以非常感谢帮助。

4 个解决方案

#1

Looping over a range of cells is inherently slow. It's faster for move the data to a Variant Array, process that, then move the results back to the sheet

在一系列细胞上循环本身就很慢。将数据移动到Variant Array，处理该数据然后将结果移回工作表会更快

Try this

尝试这个

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

Version to leverage a 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

#2

Instead of looping through cells or an array, use Range.Find and Range.FindNext to identify the matching cells.

使用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

This finds the first cell containing "100" in the Account column, verifies that the value found starts with 100 and multiplies Balance by 100. It then tries to find the next cells containing "100" until the found cell matches the FirstFoundCell.

这将找到Account列中包含“100”的第一个单元格，验证找到的值是否以100开始并将Balance乘以100.然后尝试查找包含“100”的下一个单元格，直到找到的单元格与FirstFoundCell匹配为止。

To reference a Table, simply replace the Match functions with structured references to the Table columns:

要引用表，只需将匹配函数替换为表列的结构化引用：

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

#3

You don't need to loop at all for this - range or array.

你根本不需要循环 - 范围或数组。

This code is a VBA single shot range equivalent of IF(LEFT(A2,3)="100", A2*100,A2)

此代码是相当于IF的VBA单发射程（LEFT（A2,3）=“100”，A2 * 100，A2）

code

码

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

#4

This might be a bit faster (not tested, but pretty similar to @brettdj's answer):

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

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

#1