VBA to get the value of the cell above (1 Viewer)

DanG

Registered User.

Local time

Yesterday, 21:16

Joined

Nov 4, 2004

Messages

477

• Jun 25, 2008

• #1

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...

Code:Copy to clipboard

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

Retired

Local time

Today, 05:16

Joined

Jun 2, 2003

Messages

12,701

• Jun 25, 2008

• #2

Try

Code:Copy to clipboard

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

Registered User.

Local time

Yesterday, 21:16

Joined

Nov 4, 2004

Messages

477

• Jun 25, 2008

• #3

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...

Code:Copy to clipboard

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

 

Brianwarnock

Retired

Local time

Today, 05:16

Joined

Jun 2, 2003

Messages

12,701

• Jun 25, 2008

• #4

Hmm just ran it no problem.

Brian

 

DanG

Registered User.

Local time

Yesterday, 21:16

Joined

Nov 4, 2004

Messages

477

• Jun 25, 2008

• #5

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

Retired

Local time

Today, 05:16

Joined

Jun 2, 2003

Messages

12,701

• Jun 25, 2008

• #6

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

Registered User.

Local time

Yesterday, 21:16

Joined

Nov 4, 2004

Messages

477

• Jun 25, 2008

• #7

Thanks Brian...

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

Thanks for the last tip as well.

 

Brianwarnock

Retired

Local time

Today, 05:16

Joined

Jun 2, 2003

Messages

12,701

• Jun 25, 2008

• #8

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

Registered User.

Local time

Yesterday, 21:16

Joined

Nov 4, 2004

Messages

477

• Jun 25, 2008

• #9

I always love the explanations!

Thanks again.