压缩“If陈述句”中的重复代码-编程知识-白鹭情

我有以下代码，但在“if”陈述句的每个条件之后，代码几乎相同，除了第一行。我想知道是否有办法减少代码，以便代码只运行一次。

For Each cel In AddOrRemoveRng
On Error Resume Next

If cel = "Add" And Not IsEmpty(cel.Offset(0, 1)) And Not cel.Offset(0, 3) = "0" Then
    AssetList.Cells(Asset_Row, Column_A).Value = "Add"
    AssetList.Cells(Asset_Row, Column_B).Value = cel.Offset(0, 3) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 4) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 5) 

    Asset_Row = Asset_Row   1

ElseIf cel = "Remove" And Not cel.Offset(0, 3) = "0" Then
    TelecomAssetList.Cells(Asset_Row, Column_A).Value = "Retire"
    AssetList.Cells(Asset_Row, Column_B).Value = cel.Offset(0, 3) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 4) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 5)
    
    Asset_Row = Asset_Row   1

ElseIf cel = "Update" Then
    TelecomAssetList.Cells(Asset_Row, Column_A).Value = "Update"
    AssetList.Cells(Asset_Row, Column_B).Value = cel.Offset(0, 3) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 4) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 5)
    
    Asset_Row = Asset_Row   1


End If


Next cel

uj5u.com热心网友回复：

缩短重复代码

请注意，add案例使用AssetList，而其他两个案例使用TelecomAssetList。
我已经AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 5).Value用AssetList.Cells(Asset_Row, Column_D).Value = cel.Offset(0, 5).Value(_D而不是_C)替换了：在同一个单元格中写入两次是没有意义的。
您不能On Error Resume Next如此“松散”地使用并期望代码正常作业。该宣告通常应用于一两行，并带有适当的“结束跟进”，
例如On Error Goto 0。
通过使用DoWrite布林值，您可以控制是否执行 4 条重复行。
通过LCase以小写形式使用和书写案例，您允许小写/大写拼写错误，例如Add，adD并且add被认为是相等的。
通过使用CStr，除了其主要目的之外，可能的错误值不会引发错误。

Dim DoWrite As Boolean

For Each cel In AddOrRemoveRng.Cells

    Select Case LCase(CStr(cel.Value))
    Case "add"
        If Not IsEmpty(cel.Offset(0, 1)) Then
            If CStr(cel.Offset(0, 3).Value) <> "0" Then
                AssetList.Cells(Asset_Row, Column_A).Value = "Add"
                DoWrite = True
            End If
        End If
    Case "remove"
        If CStr(cel.Offset(0, 3).Value) <> "0" Then
            TelecomAssetList.Cells(Asset_Row, Column_A).Value = "Retire"
            DoWrite = True
        End If
    Case "update"
        TelecomAssetList.Cells(Asset_Row, Column_A).Value = "Update"
        DoWrite = True
    End Select
    
    If DoWrite Then
        DoWrite = False
        AssetList.Cells(Asset_Row, Column_B).Value = cel.Offset(0, 3).Value
        AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 4).Value
        AssetList.Cells(Asset_Row, Column_D).Value = cel.Offset(0, 5).Value
        Asset_Row = Asset_Row   1
    End If

Next cel

uj5u.com热心网友回复：

我相信这应该会有所帮助：

For Each cel In AddOrRemoveRng
On Error Resume Next

If cel = "Add" And Not IsEmpty(cel.Offset(0, 1)) And Not cel.Offset(0, 3) = "0" Then
    AssetList.Cells(Asset_Row, Column_A).Value = "Add"


ElseIf cel = "Remove" And Not cel.Offset(0, 3) = "0" Then
    TelecomAssetList.Cells(Asset_Row, Column_A).Value = "Retire"
    

ElseIf cel = "Update" Then
    TelecomAssetList.Cells(Asset_Row, Column_A).Value = "Update"


End If

    AssetList.Cells(Asset_Row, Column_B).Value = cel.Offset(0, 3) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 4) 
    AssetList.Cells(Asset_Row, Column_C).Value = cel.Offset(0, 5)

    Asset_Row = Asset_Row   1

Next cel