excel 从单元格切换到区域

oknwwptz 于 2023-01-06 发布在其他

关注(0)|答案(1)|浏览(204)

Set sourceRange = Worksheets("Sheet1").Range("A1:A2") 
Set fillRange = Worksheets("Sheet1").Range("A1:A20") 
sourceRange.AutoFill Destination:=fillRange

我想在第一个空列（总是第2行）中输入一个公式，然后将该公式复制到寄存器中的所有行（它是从外部源复制的数据）。

Sub SetNextEmptyFormula(strFormula As Variant)
  Dim ws As Worksheet
  Dim lCol As Long, lRow As Long

  Set ws = Workbooks("myworkbook.xlsm").Worksheets("register")

  ' Always row 2 -> lRow, always in lCol
  lCol = ws.Range("A1").End(xlToRight).Column
  lRow = ws.Range("A1").End(xlDown).Row
  ws.Cells(2, lCol + i).Value = CStr(strFormula)

  Set sourceRange = ws.Range(???)
  Set fillRange = ws.Range(???)
  sourceRange.AutoFill Destination:=fillRange
End Sub

excel

来源：https://stackoverflow.com/questions/75010651/switch-from-cells-to-range

1条答案

按热度按时间

67up9zun1#

我不确定你将如何构建你的公式，但是如果你只是在寻找引用这些单元格的帮助，这将是有用的：

Option Explicit
Sub UseSub()
    
    Dim myFormula
    myFormula = "=ROW()"
    Call SetNextEmptyFormula(myFormula)
    
End Sub
Sub SetNextEmptyFormula(strFormula As Variant)

    Dim WS As Worksheet
    Dim lCol As Long, lRow As Long
    Dim fillRange As Range

    Set WS = Workbooks("myworkbook.xlsm").Worksheets("register")
   'Set WS = Workbooks("Book1.xlsm").Worksheets("register")
    
    With WS
        
        ' Always row 2 -> lRow, always in lCol
        lCol = .Range("A1").End(xlToRight).Column
        lRow = .Range("A1").End(xlDown).Row
        .Cells(2, lCol + 1).Formula = CStr(strFormula)
        
        'FillDown Only requires you reference the whole range Once, You only need "FillRange"
        'Set sourceRange = ws.Range(???)
        Set fillRange = .Range(.Cells(2, lCol + 1), .Cells(lRow, lCol + 1))
        fillRange.FillDown
    
    End With
    
End Sub

赞(0）回复(0）举报 2023-01-06

我来回答

excel 从单元格切换到区域

1条答案

相关问题

热门标签

最新问答