如何通过单元格C8、C9指定循环范围执行目标VBA代码

Alright, let's get this sorted for you. I've modified your original VBA code to include the loop functionality you need, plus added the data summation logic as described. Here's the breakdown:

Sub LoopAndSumData()
    Dim wsSite As Worksheet
    Dim wsNetwork As Worksheet
    Dim x As Long, y As Long
    Dim i As Long
    Dim copyRange As Range
    Dim pasteStartCell As Range
    Dim inputRange As Range
    Dim cumulativeRange As Range
    
    ' Set worksheet references to avoid repeated lookups and make code cleaner
    Set wsSite = ThisWorkbook.Sheets("SITE Model")
    Set wsNetwork = ThisWorkbook.Sheets("NETWORK Model")
    
    ' Pull loop start (x) and end (y) values directly from SITE Model cells
    x = wsSite.Range("C8").Value
    y = wsSite.Range("C9").Value
    
    ' Define where we start pasting cumulative values in NETWORK Model
    Set pasteStartCell = wsNetwork.Range("D27")
    
    ' Optional: Clear existing cumulative data before starting the loop
    ' Remove this line if you want to keep previous data
    pasteStartCell.CurrentRegion.ClearContents
    
    ' Run the loop from x to y, incrementing by 1 each time
    For i = x To y Step 1
        ' Update SITE Model's C8 with the current loop number
        wsSite.Range("C8").Value = i
        
        ' Replicate your original range selection logic without using Select
        With wsNetwork
            Set copyRange = .Range("D37", .Range("D37").End(xlToRight))
            Set copyRange = .Range(copyRange, copyRange.End(xlDown))
            Set copyRange = .Range(copyRange, copyRange.End(xlDown))
            Set copyRange = .Range(copyRange, copyRange.End(xlDown))
        End With
        
        ' Paste values to the next empty row in the cumulative area
        ' If you need to overwrite instead of append, change this to pasteStartCell directly
        copyRange.Copy
        pasteStartCell.End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False ' Clear the clipboard to free up memory
    Next i
    
    ' --- Handle the data summation requirement ---
    ' Replace this example range with your actual input data area in SITE Model
    Set inputRange = wsSite.Range("A1:C10")
    
    ' Define the full cumulative range we pasted values into
    Set cumulativeRange = wsNetwork.Range(pasteStartCell, pasteStartCell.End(xlDown).End(xlToRight))
    
    ' Sum the input data and cumulative values, then paste the result back to cumulative range
    ' Make sure inputRange and cumulativeRange are the same size/shape for this to work correctly
    cumulativeRange.Value = Application.WorksheetFunction.Sum(inputRange, cumulativeRange)
End Sub

• Worksheet shortcuts: Using wsSite and wsNetwork cuts down on repetitive code and makes it easier to adjust worksheet names later if needed.
• Loop setup: The loop uses exactly the values you specify in SITE Model!C8 (start) and C9 (end)—no hardcoding needed.
• Avoiding Select/Activate: This is a big one. Selecting cells can cause errors if the user clicks somewhere else while the macro runs, and it's slower. The rewritten code targets ranges directly, which is more reliable.
• Copy-paste behavior: Right now, the code appends each copied range to the end of the cumulative area. If you need to overwrite the same spot each time, just change the paste line to pasteStartCell.PasteSpecial Paste:=xlPasteValues instead of finding the next empty row.
• Summation adjustment: The inputRange is set to an example area (A1:C10). You'll need to replace this with the actual range that contains your input data in SITE Model. Also, ensure this range matches the size and shape of the cumulativeRange so the summation works as expected.

• Always test macros on a copy of your workbook first to avoid accidental data loss.
• If you get a "mismatch" error during summation, double-check that inputRange and cumulativeRange are the same dimensions (same number of rows and columns).

内容的提问来源于stack exchange，提问作者Jack