CODEWITHSUNDEEP
excelvbaloops
kruk22
kruk22

Reputation: 159

Get value of the adjacent cell if current cell is empty in VBA

Is there a way to copy the value of the adjacent cell if the current cell is empty using VBA?

Example here:

If I loop through the cells of column A and a cell is empty, copy the value of adjacent cell of column B to that empty cell in column A.

enter image description here

This should be the report I generate:

enter image description here

Upvotes: 3

Views: 3353

Answers (3)

Mrig
Mrig

Reputation: 11702

Try this:

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

Upvotes: 6

user3598756
user3598756

Reputation: 29421

you could try this

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

Upvotes: 3

gizlmo
gizlmo

Reputation: 1922

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

Upvotes: 2

Related Questions