壓縮“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

標籤：擅长 vba

上一篇：為每個列標題創建一個新作業表

下一篇：Excel中的宏在一個單元格中停止