代码之家 › 专栏 › 技术社区 › Mdh

基于另一工作表单元格的触发器填充公式

eventtrigger excel vba

Mdh · 技术社区 · 7 年前

我有四个工作表,我在F2单元中写了一个公式,它触发了所有其他的填充。我已经有了触发器私有sub来调用我的有效过程。有没有办法让这艘潜艇更有效率?

Option Explicit

Sub FillDownFormula_test2()
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 ' Filldown a formula for in column of data.
 ' Assumes a data table with headings in the first row,
 ' the formula in the second row and is the active cell.
 ' this one is tested based on set range
 ' Test results were positive
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 Dim rng As Range, rng2 As Range
 Dim rngData As Range
 Dim rngFormula As Range, rng2Formula As Range
 Dim rowData As Long
 Dim colData As Long
 Dim wb As Workbook: Set wb = ThisWorkbook
 Dim ws As Worksheet, ws2 As Worksheet

 Set ws = wb.Sheets("Feuil1")


 With ws

' Set the ranges
 Set rng = wb.Sheets("Feuil1").Range("F2")
 Set rngData = rng.CurrentRegion

' Set the row and column variables
 rowData = rngData.CurrentRegion.Rows.Count
 colData = rng.Column

' Set the formula range and fill down the formula
 Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
 rngFormula.FillDown

 'G2 is a different formulas but same on every sheets
 Set rng2 = wb.Sheets("Feuil1").Range("G2")


' Set the row and column variables
 rowData = rngData.CurrentRegion.Rows.Count
 colData = rng2.Column

' Set the formula range and fill down the formula
 Set rng2Formula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
 rng2Formula.FillDown
 End With



 With ws2
Set rng = wb.Sheets("Feuil2").Range("F2")
 Set rngData = rng.CurrentRegion

' Set the row and column variables
    rowData = rngData.CurrentRegion.Rows.Count
   colData = rng.Column

' Set the formula range and fill down the formula
 Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
 rngFormula.FillDown

 Set rng2 = wb.Sheets("Feuil2").Range("G2")


' Set the row and column variables
 rowData = rngData.CurrentRegion.Rows.Count
 colData = rng2.Column

' Set the formula range and fill down the formula
 Set rng2Formula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
     rng2Formula.FillDown



 End With
 End Sub

最后一个问题:在做第二个之前,什么是做一个区间最有效的方法,这样我就可以在做第二个区间之前输入公式。(我有六个公式要填写)

非常感谢。

1 回复 | 直到 7 年前

Maldred 7 年前

它可能看起来像这样。。。

Option Explicit

Sub FillDownFormula_test2()
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 ' Filldown a formula for in column of data.
 ' Assumes a data table with headings in the first row,
 ' the formula in the second row and is the active cell.
 ' this one is tested based on set range
 ' Test results were positive
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Dim ws As Worksheet
    Dim ws2 As Worksheet

    Set ws = wb.Sheets("Feuil1")

    With ws
        Call FillDownData(.Range("F2"), Range("F2").CurrentRegion)
        Call FillDownData(.Range("G2"), Range("G2").CurrentRegion)
    End With

    Set ws2 = wb.Sheets("Feuil2")

    With ws2
        Call FillDownData(.Range("F2"), Range("F2").CurrentRegion)
        Call FillDownData(.Range("G2"), Range("G2").CurrentRegion)
    End With

End Sub

Function FillDownData(rng As Range, rngData As Range)

    Dim rowData As Long
    Dim colData As Long
    Dim rngFormula As Range

' Set the row and column variables
    rowData = rngData.CurrentRegion.Rows.Count
    colData = rng.column
Debug.Print rowData & " " & colData
' Set the formula range and fill down the formula
    'Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
    'rngFormula.FillDown

End Function