在VBA中，如果当前单元格为空，则获取相邻单元格的值

Question

如何使用VBA在当前单元格为空的情况下复制相邻单元格的值？

示例：

如果我循环遍历A列的单元格，当一个单元格为空时，将B列相邻单元格的值复制到A列的该空单元格中。

这应该是我生成的报告：

- kruk22

Answer 1

试试这个：

Sub Demo()
    Dim rng As Range
    Dim lastRow As Long
    Dim cell as Range

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A1:A" & lastrow)
    For Each cell In rng
        If cell.Value = "" Then
            cell.Value = cell.Offset(0, 1).Value
        End If
    Next cell
End Sub

- Mrig

Answer 2

你可以尝试这个。

Sub main()    
    With ActiveSheet.UsedRange.Columns("A")
        If WorksheetFunction.CountBlank(.Cells) > 0 Then
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"
            .Value = .Value
        End If
    End With    
End Sub

- user3598756

---

这也太棒了！谢谢！ - kruk22

Answer 3

Sub movecells()

Dim usedrows As Integer
Dim tmpval As String
Dim i As Integer

usedrows = UsedRange.Rows.Count

For i = 1 To usedrows Step 1

    If Cells(i, 1).Value = "" And Cells(i, 2).Value <> "" Then

        Cells(i, 1).Value = Cells(i, 2).Value
        Cells(i, 2).Value = ""
    End If
Next i
End Sub

- gizlmo