PDA

View Full Version : Solved: how to reference previous cell value



cgannm
08-10-2009, 09:42 AM
Hi, I am seeking your assistance with code that would find empty cells in column A based on cell value equal zero then copy/reference the most previous cell with value not equal zero.

For example, A1:A5 has values 1,2,empty cell, empty cell, 3. A3,A4 are empty cells, based on zero value, it then copies A2 in A3 and A4. Result would be: 1,2,2,2,3.

Thanks,
Anna

Bob Phillips
08-10-2009, 10:24 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastVal As Variant

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastVal = .Range("A1").Value
For i = 2 To LastRow

If .Cells(i, "A").Value = "" Then

.Cells(i, "A").Value = LastVal
End If
LastVal = .Cells(i, "A").Value
Next i

End With

End Sub

cgannm
08-10-2009, 11:41 AM
XID, once again thanks for coming to my resuce.

It worked like a charm. Your time and skill are very much appreciated.

Anna