VBA公式自动填充引用列错误求助：修正跨工作表股票收益率计算

Hey there, let's work through this AutoFill problem you're hitting. The core issue is that your target worksheet uses consecutive columns for returns, but the source price columns in the "Input" sheet are spaced out. AutoFill relies on relative references to increment columns automatically, which doesn't align with the non-consecutive layout of your input data—hence the wrong column references like D5/D$5 instead of the intended F5/F$5.

Why Your Original Code Fails #

Your initial formula uses relative column references (C[1]) in FormulaR1C1. When you run AutoFill, Excel automatically increments that relative column index for each new target column. But since your Input sheet's price columns aren't consecutive, this incremental shift doesn't match the actual columns you need to reference.

The Solution: Manual Column Mapping Instead of AutoFill #

Instead of relying on AutoFill, we'll explicitly map each target column to the correct price column in the Input sheet. This gives you full control over which Input column is used for each return calculation.

Here's the revised code, with breakdowns:

Sub CalculateStockReturns()
    Dim wsInput As Worksheet
    Dim wsTarget As Worksheet
    Dim inputPriceCols As Collection
    Dim lastTargetCol As Integer
    Dim targetCol As Integer
    Dim inputCol As Integer
    
    ' Set references to your worksheets (replace "TargetSheet" with your actual target sheet name)
    Set wsInput = ThisWorkbook.Worksheets("Input")
    Set wsTarget = ThisWorkbook.Worksheets("TargetSheet")
    
    ' Step 1: Collect column numbers of price data in Input sheet
    ' Assumption: Row 3 in Input sheet has stock names/price column headers
    Set inputPriceCols = New Collection
    For col = 1 To wsInput.Cells(3, wsInput.Columns.Count).End(xlToLeft).Column
        If wsInput.Cells(3, col).Value <> "" Then
            inputPriceCols.Add col ' Only add columns with valid header data
        End If
    Next col
    
    ' Step 2: Get last column in target sheet (matches your original logic)
    lastTargetCol = wsTarget.Cells(3, wsTarget.Columns.Count).End(xlToLeft).Column
    
    ' Step 3: Write formula to each target column with correct Input column mapping
    For targetCol = 2 To lastTargetCol ' Start at column B (2) as per your original setup
        ' Avoid exceeding the number of price columns in Input
        If targetCol - 1 <= inputPriceCols.Count Then
            inputCol = inputPriceCols(targetCol - 1) ' Map target column to Input price column
            
            ' Formula uses absolute row reference for base price (Row 5)
            wsTarget.Cells(4, targetCol).FormulaR1C1 = _
                "='" & wsInput.Name & "'!RC" & inputCol & "/'" & wsInput.Name & "'!R5C" & inputCol & "*100"
                
            ' Optional: Uncomment to fill formula down rows beyond row 4
            ' wsTarget.Range(wsTarget.Cells(4, targetCol), wsTarget.Cells(wsTarget.Cells(wsTarget.Rows.Count, targetCol).End(xlUp).Row, targetCol)).FillDown
        End If
    Next targetCol
End Sub

Key Details: #

• Explicit Column Mapping: We first collect all price columns in the Input sheet (using row 3 as the header row—adjust this if your headers are in a different row). This ensures we only reference columns that contain stock price data.
• Absolute Row Reference: The formula uses R5C" & inputCol for the denominator, locking the row to 5 (your base price row). This means when you fill the formula down rows later, the denominator always points to row 5 of the correct Input column.
• Flexibility: If you already know the exact column numbers of your price data (e.g., columns 6, 9, 12 for F, I, L), you can replace the collection setup with a static array like inputPriceCols = Array(6,9,12) for a simpler implementation.

Simplified Version for Known Columns #

If you don't need to dynamically collect columns, use this trimmed-down code:

Sub CalculateStockReturnsStatic()
    Dim wsInput As Worksheet
    Dim wsTarget As Worksheet
    Dim inputPriceCols As Variant
    Dim lastTargetCol As Integer
    Dim targetCol As Integer
    
    Set wsInput = ThisWorkbook.Worksheets("Input")
    Set wsTarget = ThisWorkbook.Worksheets("TargetSheet")
    
    ' Define your Input price columns directly (e.g., F=6, I=9, L=12)
    inputPriceCols = Array(6, 9, 12)
    
    lastTargetCol = wsTarget.Cells(3, wsTarget.Columns.Count).End(xlToLeft).Column
    
    For targetCol = 2 To lastTargetCol
        If targetCol - 1 <= UBound(inputPriceCols) + 1 Then
            wsTarget.Cells(4, targetCol).FormulaR1C1 = _
                "='" & wsInput.Name & "'!RC" & inputPriceCols(targetCol - 2) & "/'" & wsInput.Name & "'!R5C" & inputPriceCols(targetCol - 2) & "*100"
        End If
    Next targetCol
End Sub

This approach eliminates AutoFill's guesswork and ensures each return column references exactly the right price column in your Input sheet.

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