VBA to get the value of the cell above

[DanG]

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

 

[Brianwarnock]

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

 

[DanG]

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

 

[Brianwarnock]

Hmm just ran it no problem.

Brian

 

[DanG]

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!

 

[Brianwarnock]

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

 

[DanG]

Thanks Brian...

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

Thanks for the last tip as well.

 

[Brianwarnock]

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

 

[DanG]

I always love the explanations!

Thanks again.