通过vba函数实现:写入一个公式到单元格中,并自动填充该公式到该列指定范围中。
❤ 代码1(在当前文件中添加公式):
Sub 在当前文件中自动填充一列公式(sheetName As String, cellStr As String, r As String, formulaStr As String)
'
'sheetName:sheet名
'cellStr:要填充的起始单元格,如“A1”
'r:要写入的范围,如“A1:A100”
'formulaStr:公式字符串
'
'激活sheet
ThisWorkbook.Sheets(sheetName).Activate
'选中要填入公式的单元格
ThisWorkbook.ActiveSheet.Range(cellStr).Select
'写入公式
Selection.Formula = formulaStr
'选中该单元格
ThisWorkbook.ActiveSheet.Range(cellStr).Select
'自动填充
Selection.AutoFill Destination:=ThisWorkbook.ActiveSheet.Range(r), Type:=xlFillDefault
ThisWorkbook.ActiveSheet.Range(r).Select
'保存
ThisWorkbook.Save
End Sub
❤ 代码2(在目标文件中添加公式):
Sub 在目标文件中自动填充一列公式(srcPath As String, sheetName As String, cellStr As String, r As String, formulaStr As String)
'
'srcPath:文件路径
'sheetName:sheet名
'cellStr:要填充的起始单元格
'r:要写入的范围
'formulaStr:公式
'
'打开目标Excel文件
Set srcWb = Workbooks.Open(srcPath)
srcWb.Sheets(sheetName).Activate
'选中单元格
srcWb.ActiveSheet.Range(cellStr).Select
'写入公式
Selection.Formula = formulaStr
srcWb.ActiveSheet.Range(cellStr).Select
'自动填充到指定范围
Selection.AutoFill Destination:=srcWb.ActiveSheet.Range(r), Type:=xlFillDefault
srcWb.ActiveSheet.Range(r).Select
'保存
srcWb.Save
'关闭
srcWb.Close
End Sub