CODEWITHSUNDEEP
excelvba
Cookiewich
Cookiewich

Reputation: 3

Copy and paste if one cell is blank and the other is not

So data gets pasted in to column B as the code keeps running it'll do a condition check to see there's any values in column B and paste a value in to the adjacent column A. I need to make it so it does two condition checks:

If there's values in column b, but then to check if there's values in column A before pasting so it doesn't overwrite different data that's been pasted already. 

For Each Cell In y.Sheets("Compiled").Range("A:B")
    If Range("B:B").Value <> "" And Range("A:A").Value = "" Then
    Cell.Offset(0, -1).PasteSpecial xlPasteValues
    End If
Next

Upvotes: 0

Views: 42

Answers (2)

Jchang43
Jchang43

Reputation: 891

Here's a version of the code that implements the lastrow value that dwirony mentioned in their answer. This also throws everything in arrays, so it might go a bit faster if you have a really large dataset.

Option Explicit
Sub test()
    Dim ACol As Variant
    Dim BCol As Variant
    Dim lastrow As Long
    Dim i As Long

    lastrow = Range("B:B").Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).row
    BCol = Range("B1:B" & lastrow).Value
    ACol = Range("A1:A" & lastrow).Value

    For i = LBound(BCol) To UBound(BCol)
        If IsEmpty(ACol(i, 1)) And Not IsEmpty(BCol(i, 1)) Then
            ACol(i, 1) = BCol(i, 1)
        End If
    Next i

    Range("A1:A" & lastrow).Value = ACol
End Sub

Upvotes: 0

dwirony
dwirony

Reputation: 5450

You were close, don't try to loop over a multiple column range:

Sub Test()

For Each Cell In y.Sheets("Compiled").Range("B:B")
    If Cell.Value <> "" And Cell.Offset(0, -1).Value = "" Then
        Cell.Offset(0, -1).Value = Cell.Value
    End If
Next

End Sub

NOTE: You are looping through every cell in Range("B:B") which is probably unnecessary. It'd be better if you use a lastrow value, or a static range like Range("B2:B1000"). Or you could use a criteria to exit your loop like If Cell.Value = "" Then Exit For.

Upvotes: 1

Related Questions