Check if Cell Is Empty

[vickyho1008]

please see attached spreadsheet, if you copy cells from K3 to N92 into A3 to D92, data will be automatically shown up in columns F to I.

But there's a problem, whenever there's a cell contains 0% in col B-D, the corresponding cell in col G-I will be empty which affects the calculation therafter, can anyone help on this?

thanks

[rbrhodes]

Vicky,

Changed: If cell.Value <> Empty Then

To: If cell.Value <> Empty Or cell.Value = 0 Then

Seems to work for me.

Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim cell As Range 
    If Not Intersect(Target, Sheets("raw data").Range("A3:A65536")) Is Nothing Then 
        For Each cell In Target 
            If cell.Value <> Empty Or cell.Value = 0 Then 
                cell.Offset(0, 5).Formula = "=" & cell.Address(False, False) 
            Else 
                cell.Offset(0, 5).ClearContents 
            End If 
        Next cell 
    End If 
     
    If Not Intersect(Target, Sheets("raw data").Range("B3:B65536")) Is Nothing Then 
        For Each cell In Target 
            If cell.Value <> Empty Or cell.Value = 0 Then 
                cell.Offset(0, 5).Formula = "=" & cell.Offset(-1, 5).Address(False, False) & "*(1+" & cell.Address(False, False) & ")" 
            Else 
                cell.Offset(0, 5).ClearContents 
            End If 
        Next cell 
    End If 
     
    If Not Intersect(Target, Sheets("raw data").Range("C3:C65536")) Is Nothing Then 
        For Each cell In Target 
            If cell.Value <> Empty Or cell.Value = 0 Then 
                cell.Offset(0, 5).Formula = "=" & cell.Offset(-1, 5).Address(False, False) & "*(1+" & cell.Address(False, False) & ")" 
            Else 
                cell.Offset(0, 5).ClearContents 
            End If 
        Next cell 
    End If 
     
    If Not Intersect(Target, Sheets("raw data").Range("D3:D65536")) Is Nothing Then 
        For Each cell In Target 
            If cell.Value <> Empty Or cell.Value = 0 Then 
                cell.Offset(0, 5).Formula = "=" & cell.Offset(-1, 5).Address(False, False) & "*(1+" & cell.Address(False, False) & ")" 
            Else 
                cell.Offset(0, 5).ClearContents 
            End If 
        Next cell 
    End If 
End Sub 

Cheers,

dr

[Dave Hawley]

Use IsEmpty

Sub a() 
    Range("A1") = 0 
    MsgBox IsEmpty(Range("A1")) 
    Range("A1").Clear 
    MsgBox IsEmpty(Range("A1")) 
End Sub 

[vickyho1008]

by using "If cell.Value <> Empty Or cell.Value = 0", it solves the problem of 0% cells, but anothe problems raises, if u delete some monthly returns in Col B-D (e.g. if u delete the last 3 months' returns), the corresponding cells in Col G-I don't clear contents..

I think it's the same thing again, as VB treats 0 as empty, so it will never jump to the else statement

[peertje888]

If cell.Value <> Empty Or cell.Value = 0

Didn't work for me either...even empty cells returned 0 for me....I used:

If Len(cell.Value) > 0

(Don't want to be a grave digger here, but I bumped on this thread via google, and suppose more people will....)

[AAE]

peeertje888,

We're glad to have you at Ozgrid.

However, do not post your questions in a thread started by others - - this is known as thread hijacking and is a violation of the forum rules.
See the link in my signature for access to the Forum Rules, which if you have not read, take time to read them to avoid infractions.

Start a new thread for your question.

[rbrhodes]

Looks more like answer to me...

[AAE]

peertje88,

Seems I owe you an apology. Since I publicly called you on posting in an old thread, I am apologizing publicly.
As rbrhodes points out you did provide a answer - and did not ask a question.

We get enough of these errors of posting questions in old threads and when I saw your post count as "1", I really didn't take enough time to review your post.

rbrhodes -- thanks for pointing this out.