請幫我為這個vba代碼創建一個回圈

我有 2 張表，sourcesheet 和 acct 表。從 sourceSheet 我需要將 sourceSheet.Range(Cells(14, 3),Cells(14, 8)) 中的值復制到 AcctSheet.range(Cells(2, 11),Cells(7, 11))，但是每個sourcesheet 中的單元格與 acctsheet 完全匹配，這樣

sourceSheet.Cells(14, 3) = AcctSheet.Cells(2, 11)
sourceSheet.Cells(14, 4) = AcctSheet.Cells(3, 11)
sourceSheet.Cells(14, 5) = AcctSheet.Cells(4, 11) and so on until
sourceSheet.Cells(14, 8) = AcctSheet.Cells(7, 11)

完整代碼在這里，但希望回圈這個。

sourceSheet.Activate
    'EQ
    If IsEmpty(sourceSheet.Cells(14, 3).Value) Then
        AcctSheet.Cells(2, 11).Value = sourceSheet.Cells(7, 1).Value   
    ElseIf sourceSheet.Cells(14, 3).Value < sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(2, 11).Value = sourceSheet.Cells(14, 3).Value 
    ElseIf sourceSheet.Cells(14, 3).Value > sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(2, 11).Value = sourceSheet.Cells(7, 1).Value   
    End If
    'WS
    If IsEmpty(sourceSheet.Cells(14, 4).Value) Then
        AcctSheet.Cells(3, 11).Value = sourceSheet.Cells(7, 1).Value   
    ElseIf sourceSheet.Cells(14, 4).Value < sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(3, 11).Value = sourceSheet.Cells(14, 4).Value 
    ElseIf sourceSheet.Cells(14, 4).Value > sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(3, 11).Value = sourceSheet.Cells(7, 1).Value   
    End If
    'TO
    If IsEmpty(sourceSheet.Cells(14, 5).Value) Then
        AcctSheet.Cells(4, 11).Value = sourceSheet.Cells(7, 1).Value   
    ElseIf sourceSheet.Cells(14, 5).Value < sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(4, 11).Value = sourceSheet.Cells(14, 5).Value 
    ElseIf sourceSheet.Cells(14, 5).Value > sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(4, 11).Value = sourceSheet.Cells(7, 1).Value   
    End If
    'FL
    If IsEmpty(sourceSheet.Cells(14, 6).Value) Then
        AcctSheet.Cells(5, 11).Value = sourceSheet.Cells(7, 1).Value   
    ElseIf sourceSheet.Cells(14, 6).Value < sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(5, 11).Value = sourceSheet.Cells(14, 6).Value 
    ElseIf sourceSheet.Cells(14, 6).Value > sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(5, 11).Value = sourceSheet.Cells(7, 1).Value   
    End If
    'FR
    If IsEmpty(sourceSheet.Cells(14, 7).Value) Then
        AcctSheet.Cells(6, 11).Value = sourceSheet.Cells(7, 1).Value   
    ElseIf sourceSheet.Cells(14, 7).Value < sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(6, 11).Value = sourceSheet.Cells(14, 7).Value 
    ElseIf sourceSheet.Cells(14, 7).Value > sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(6, 11).Value = sourceSheet.Cells(7, 1).Value   
    End If
    'TR
    If IsEmpty(sourceSheet.Cells(14, 8).Value) Then
        AcctSheet.Cells(7, 11).Value = sourceSheet.Cells(7, 1).Value   
    ElseIf sourceSheet.Cells(14, 8).Value < sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(7, 11).Value = sourceSheet.Cells(14, 8).Value 
    ElseIf sourceSheet.Cells(14, 8).Value > sourceSheet.Cells(7, 1).Value Then
        AcctSheet.Cells(7, 11).Value = sourceSheet.Cells(7, 1).Value   
    End If

uj5u.com熱心網友回復：

你正在尋找這樣的東西嗎？

Option Explicit

Sub test()
    Dim sourceSheet As Worksheet, acctSheet As Worksheet
    Dim i As Long
    Dim sourceCell As Range, targetCell As Range, defaultCell As Range
    
    Set defaultCell = sourceSheet.Cells(7, 1)
    
    For i = 3 To 8
    
        Set sourceCell = sourceSheet.Cells(14, i)
        Set targetCell = acctSheet.Cells(i - 1, 11)
        
        If IsEmpty(sourceCell) Then
            targetCell.Value2 = sourceCell.Value2
        ElseIf sourceCell.Value2 < defaultCell.Value2 Then
            targetCell.Value2 = sourceCell.Value2
        ElseIf sourceCell.Value2 > defaultCell.Value2 Then
            targetCell.Value2 = defaultCell.Value2
        End If
        
    Next i
    
End Sub

嚴格來說，我認為您不需要.Value2在每個單元格之后都包含，因為 VBA 在閱讀代碼時有點使用它作為默認值，但這并沒有什么壞處。

對單元格參考使用變數也不是絕對必要的，但我發現它更容易，尤其是當我稍后需要編輯單元格參考時。

標籤：擅长 vba 循环 if 语句 条件语句

上一篇：為什么我不能在C中列印指標的值？

下一篇：過濾范圍復制粘貼值并創建新作業表