excel - 在查看 ColA 的值时将 1 添加到 ColB

Question

1在ColB查看值时，我一直在尝试遍历要添加的范围，ColA并且我想将 1 从高值添加到低值，其中 0 将为空。

您的帮助将不胜感激。

我的尝试。

Dim lastRow As Integer
lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    If Sheet1.Range("A2" & lastRow).Value > 0 Then
        Range("B2" & lastRow).Value = 1
        ElseIf Sheet1.Range("A2").Value = 0 Then
        Range("B2" & lastRow).Value = ""
    End If

第二次尝试

Dim lastRow As Integer
lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Dim c As Range
For Each c In Range("A2:A50")
    If c.Value > 0 Then
      Sheet1.Range("B2" & lastRow).Value = 1
    End If
Next c

像这样

标签： excelvba

Answer 1

如果另一列中的值不等于 0，则写入列

• 请注意，两种解决方案的作用相同。
• 对于数组版本，假定范围至少有两个单元格 ( A2:A3)。如果A2:A2，它将失败。

Option Explicit

Sub SlowRange()

    Dim LastRow As Long
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
    Dim rg As Range: Set rg = Sheet1.Range("A2:A" & LastRow)
    
    Dim c As Range
    For Each c In rg.Cells
        If c.Value <> 0 Then
            c.Offset(, 1).Value = 1
        'Else
        '    c.Offset(, 1).Value = Empty
        End If
    Next c

End Sub

Sub FastArray()

    Dim LastRow As Long
    LastRow = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
    Dim rg As Range: Set rg = Sheet1.Range("A2:A" & LastRow)
    Dim Data As Variant: Data = rg.Value
    
    Dim r As Long
    For r = 1 To UBound(Data, 1)
        If Data(r, 1) <> 0 Then
            Data(r, 1) = 1
        Else
            Data(r, 1) = Empty
        End If
    Next r

    rg.Offset(, 1).Value = Data

End Sub