如何使用Excel VBA自动填充'x'行？

Question

如何使用Excel VBA自动填充'x'行？

3

我正在做的总体思路是通过用户界面(UserForm)获取用户输入，将这些数据添加到一行中，然后我想直接在该原始行下方复制该行“x”次。在此情况下，“x”行数也由用户输入的值确定。

例如，假设用户填写了UserForm并选择了'x'为5。我的VBA代码将获取他们输入的信息，将其放置在第2行，然后基本上将粘贴该行到下面的4个行中。最终结果大致如下(EXCEPT EVERY CELL IN COLUMN C WOULD BE '44', I'M NOT SURE WHY THAT COLUMN DECIDED TO INCREMENT BY 1, BUT THATS ANOTHER THING I NEED HELP FIXING):

我假设我可以编写VBA代码来复制并粘贴行x次，但是我认为自动填充会更容易，因为每行的值都相同。我只是不知道如何指定一个范围，该范围将根据用户输入而变化。这是我的自动填充特定范围的代码，我不确定如何将其更改为可变范围。


numOfLines = (LastL - FirstL) + 1    'don't worry about how I get this, it's a number that I know is correct

   With Sheets("QuoteCSV")
    
        Sheets("QuoteCSV").Select
        
        ' Get the current row
        Dim curRow As Long
        If .Range("B1") = "" Then
            curRow = 1
        Else
            curRow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
        End If
        
        ' Add items to the first row (row 2)
        .Cells(curRow, 1) = fson
        .Cells(curRow, 2) = fdiv
        .Cells(curRow, 3) = fcnum
        .Cells(curRow, 4) = fcponum
        .Cells(curRow, 5) = frdate
        .Cells(curRow, 6) = cname
        .Cells(curRow, 7) = add1
        .Cells(curRow, 8) = add2
        .Cells(curRow, 9) = city
        .Cells(curRow, 10) = state
        .Cells(curRow, 11) = zip
        
        
        'Now I want to take that row I just made and autofill it down 'numOfLines' rows

        Range("A2:K2").Select
        Application.CutCopyMode = False
        Selection.AutoFill Destination:=Range("A2:K6"), Type:=xlFillDefault 
        'need to change this line to use numOfRows somehow instead of hard-coded range
        
        
    End With
    ```

Any help is appreciated!

- actuallife

2个回答

2

另一种不需要任何自动填充的方法是：您可以一次性填写整个范围。

.Range(.Cells(curRow, 1), .Cells(numOfLines, 1)).Value = fson
.Range(.Cells(curRow, 2), .Cells(numOfLines, 2)).Value = fdiv
.Range(.Cells(curRow, 3), .Cells(numOfLines, 3)).Value = fcnum
.Range(.Cells(curRow, 4), .Cells(numOfLines, 4)).Value = fcponum
.Range(.Cells(curRow, 5), .Cells(numOfLines, 5)).Value = frdate
.Range(.Cells(curRow, 6), .Cells(numOfLines, 6)).Value = cname
.Range(.Cells(curRow, 7), .Cells(numOfLines, 7)).Value = Add1
.Range(.Cells(curRow, 8), .Cells(numOfLines, 8)).Value = Add2
.Range(.Cells(curRow, 9), .Cells(numOfLines, 9)).Value = city
.Range(.Cells(curRow, 10), .Cells(numOfLines, 10)).Value = State
.Range(.Cells(curRow, 11), .Cells(numOfLines, 11)).Value = zip

- Siddharth Rout

我只是复制粘贴了一下，打了点字:D - Siddharth Rout

“将项目添加到第一行（第2行）”。我的代码是基于这个评论的 :) - Siddharth Rout

1

这取决于用户如何计算 numOfLines。 - Siddharth Rout

1

说实话，读完 numOfLines = (LastL - FirstL) + 1 'don't worry about how I get this, it's a number that I know is correct 这段代码后，我决定将这部分留给 OP :D - Siddharth Rout

谢谢！我想我可能会采用BigBen的方式，因为它的代码行数比较少，但你的解决方案很合理，看起来也应该有效！ - actuallife

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- BigBen · Accepted Answer

也许可以使用Resize，而不是使用AutoFill:

With .Range("A2:K2")
    .Resize(numOfRows).Value = .Value
End With