vba - 将值复制到下一个空单元格

Question

我想找到空单元格并复制其中的值:

值:“10/11/2017”和"is"应复制到第 7 行(colB 和 colC)。

我有:

Sub add_value()

Dim wbA As Workbook
Dim wsA As Worksheet

Set wbA = ActiveWorkbook
Set wsA = wbA.Worksheets("Sheet1")

Dim nrow As Long

nrow = 6

    Do Until wsA.Range("B" & nrow).Value = ""
        wsA.Range("B" & nrow).Value = wsA.Range("B3").Value
        wsA.Range("C" & nrow).Value = wsA.Range("C3").Value
            Exit Sub
        nrow = nrow + 1
    Loop

End Sub

我的循环出了问题，我不知道如何修复它。

Answer 1

无需循环遍历行，直到找到空行。您可以将整个子部分替换为:

Sub add_value()
    With ThisWorkbook.Worksheets("Sheet1")
        .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(1, 2).Value = .Range("B3:C3").Value
    End With
End Sub

<小时/>

根据您的评论，要添加边框，您可以像这样重新组织代码:

Sub add_value()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws.Cells(ws.Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(1, 2)
        .Value = ws.Range("B3:C3").Value
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
    End With
End Sub