If cell contains text, copy above cell and paste loop

[dinokovac93]

Hi guys, I'm having trouble running a VBA. Seems fairly simple but not too sure how to do this. What I'm doing is going through every cell within a column. If a cell contains "N/A", then the vba will copy the cell above and paste it into that specific "N/A" cell. The VBA should loop until there are no more cells to go through.

Sub copyPasteTest()
Dim i As Long


For i = 4 To 4000
If Cells(i, 1).Value = "#N/A" Then
Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0)
End If

Next i


End Sub

 

[jproffer]

Try this:

Sub copyPasteTest()
Dim i As Long
Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = 4 To LR 'or if you prefer 4000, that works too...just an idea
    If IsError(Cells(i, 1).Value) = True Then
        Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0)
    End If
Next i


End Sub

 

[dinokovac93]

Thank you, this is perfect.

Try this:

Sub copyPasteTest()
Dim i As Long
Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = 4 To LR 'or if you prefer 4000, that works too...just an idea
    If IsError(Cells(i, 1).Value) = True Then
        Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0)
    End If
Next i


End Sub

 

[jproffer]

You're very welcome. Glad it worked for you . Thanks for the feedback.