VBA:跳过范围内的单元格

Question

我正在尝试在 C 列中填写公式，如下所示:

LastRow = Range("A65536").End(xlUp).Row
Range(Cells(1, 3), Cells(LastRow, 3)).Formula = "=A1*B1"

而且效果很好。但是是否可以跳过 C 列中的那些单元格，例如其值 > 0 ？

A B C -> A B C
3 2 0    3 2 6
3 4 0    3 4 12
5 6 5    5 6 5  <- this value is not updated

Answer 1

为此，您必须对数据的外观进行轻微更改。例如，您需要将“标题”添加到第一行。我们这样做的原因是因为我们将使用 AutoFilter

假设您的数据如下所示

现在使用此代码

Sub Sample()
    Dim ws As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws

        '~~> Remove any filters
        .AutoFilterMode = False

        '~~> Get lastrow in column c
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row

        '~~> Filter the col C to get values which have 0
        With .Range("C1:C" & lRow)
            .AutoFilter Field:=1, Criteria1:="=0"

            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible)

            '~~> Assign the formula to all the cells in one go
            If Not copyFrom Is Nothing Then _
            copyFrom.Formula = "=A" & copyFrom.Row & "*B" & copyFrom.Row
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

输出