时间戳

首页 > 解决方案 > 具有动态范围的自动填充公式

excel - 具有动态范围的自动填充公式

问题描述

我在 G 列上有我要分离的数据组合。mid/find 的公式工作正常，但是当我尝试添加我在底部在线找到的自动填充代码时，它会出错。

Range("H2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-1],FIND(""ASL"",RC[-1])+4,3)"

Range("I2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],FIND(""DEPT"",RC[-2])+5,2)"

Range("J2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-3],FIND(""ST"",RC[-3])+3,2)"

Range("K2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-4],FIND(""REIN"",RC[-4])+5,5)"

Range("H2:K2").Select
Selection.AutoFill Destination:=Range("H2:K" & Range("A" & Rows.Count).End(xlUp))

标签： excelvbaexcel-formulaautofill

解决方案

您可以通过将公式放入数组并将每个公式写入列范围来将公式写入每一列。你不需要使用AutoFill. 此代码会将公式插入到列 H - K 从第 2 行到 A 列的最后一行（值）。请根据需要更改工作表。代码中提供了注释。

Sub InsertFormulasInColumnsWithArray()

'Set your worksheet variable, change the worksheet as needed
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

'Put all your formulas into an array. the `_` connects each section into one line of code, easier to read.

Dim formulaArr As Variant: formulaArr = Array("=MID(RC[-1],FIND(""ASL"",RC[-1])+4,3)", _
"=MID(RC[-2],FIND(""DEPT"",RC[-2])+5,2)", "=MID(RC[-4],FIND(""REIN"",RC[-4])+5,5)", _
"=MID(RC[-4],FIND(""REIN"",RC[-4])+5,5)")


x = 8 'assign the variable `x` to the first column `H = 8`

    With ws 'use the With statement to ensure you are adding the formulas to the correct worksheet
    
        For i = LBound(formulaArr) To UBound(formulaArr) 'loop through the 4 formulas in your array
        
            'Starting at `row 2`, `column 8` (x = 8)`,
            'resize the the range to the value of the last row in `Column A`
            'you subtract `-1` because you start the range on `Row 2`
            'Write the first formula to the range in `Column H`,
            'in the second loop you will write the second formula in Column I, etc.

            .Cells(2, x).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 1).FormulaR1C1 = formulaArr(i)
            
            x = x + 1 'increase x by 1 to select the next column
            
        Next i 'Loop to the next formula in the array, until the last formula is done
    End With

End Sub

推荐阅读