VBScript根据列& A& quot;自动向下拖动formla到最后一行。 [英] VBScript to automatically drag down formla to last row based on column "A"

问题描述

Hi Folks -



我正在努力寻找能够从"N3"中拖出公式的解决方案。直到A列的最后一行数据。




这是我到目前为止：

Hi Folks -

I'm struggling with a solution to be able to drag down a formula from "N3" down to the last row of data for Column A.

Here is what I have thus far:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook1= objExcel.Workbooks.Open("C:\Users\chtakac\Documents\Z\ZZ\Employees Accessxls")
Set objWorksheet = objWorkbook1.Worksheets("Sheet1")
	
Set sourceRange = objWorksheet.Range("A")
Set fillRange = objWorksheet.Range("N3:N30")
sourceRange.AutoFill fillRange


objWorkbook1.Save
objWorkbook1.Close

如你所见，我可以设置硬编码范围，但我不想这样做。我需要动态。


有人可以帮忙吗？

As you can see, I can set a hardcoded range, but I dont want to do that. I need it dynamic.
Can anyone please assist?

推荐答案

  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = True
  
  Set objWorkbook1 = objExcel.Workbooks.Open("C:\Users\chtakac\Documents\Z\ZZ\Employees Accessxls")
  Set objWorksheet = objWorkbook1.Worksheets("Sheet1")

  'Define constant from Excel
  xlUp = -4162
  'Get the last used cell in column A
  Set LastCell = objWorksheet.Range("A" & objWorksheet.Rows.Count).End(xlUp)
  'The cell(s) to fill down
  Set SourceRange = objWorksheet.Range("N3")
  'Over this range
  Set FillRange = objWorksheet.Range("N3:N" & LastCell.Row)
  'Do it
  SourceRange.AutoFill FillRange

  objWorkbook1.Save
  objWorkbook1.Close

这篇关于VBScript根据列& A& quot;自动向下拖动formla到最后一行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！