I am finding I can tweak VBA and understand it, but not much good at writing it myself.

I would like to loop through a column and if there is no value in the current cell I want that cell to equal the value of the cell above it.

Here is what I have...


Sub NewSSN()

Dim myrange As Range
Dim Cell As Range
Dim rngOutput As Range

Set myrange = Range("$a$1", Range("a65536").End(xlUp))
Set rngOutput = ActiveCell

'' On Error Resume Next

For Each Cell In myrange
If Len(Cell) = 0 Then
rngOutput = Cell.Offset(-1).Value

End If
Next Cell

End Sub



I think I am close but not quite there.

Thank you

Try
Public Sub NewSSN()

Dim myrange As Range
Dim Cell As Range


Set myrange = Range("$a$1", Range("a65536").End(xlUp))

'' On Error Resume Next

For Each Cell In myrange
If Len(Cell) = 0 Then
Cell.Value = Cell.Offset(-1).Value

End If
Next Cell

End Sub

Brian

I get and error message:
"Application-difined or Object-defined error"
Which is the same error I got with my original code.

It doesn't seem to like the...

Cell.Value = Cell.Offset(-1).Value

Hmm just ran it no problem.

Brian

I had the same error (with original code) on two machines between today and yesterday.

Let me make sure the problem isn't with my data (datatype...corrupt sheet...?).

Thank you for your help!

BTW you do know that it will treat a blank as a value, if you want to fill in blanks and empty cells code

If Cell = "" Or Cell = " " Then

I never use Cell it is too close to the function Cells for comfort I just use c

For each c in myrange etc


Brian

Thanks Brian...

Code is good, data has issues (imported from other system, need to clean).

Thanks for the last tip as well.

Got your last post while typing.
Your original code did not fail for me just did what you asked and I expected.

This
Set rngOutput = ActiveCell
sets the rngoutput to the cell your cursor is at, and thus that is where all the output is placed.
Thought you might appreciate an explanation.

Brian

I always love the explanations!

Thanks again.