Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Check if Cell Is Empty

  1. #1
    Join Date
    9th September 2004
    Posts
    88

    Check if Cell Is Empty

    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Vb Treats 0 As Empty Too

    Vicky,

    Changed: If cell.Value <> Empty Then

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

    Seems to work for me.


    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,705

    Re: Vb Treats 0 As Empty Too

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

  4. #4
    Join Date
    9th September 2004
    Posts
    88

    Re: Vb Treats 0 As Empty Too

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd October 2010
    Posts
    1

    Re: Check if Cell Is Empty

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Check if Cell Is Empty

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

    Forum Rules | Message to Cross Posters | How to use Tags

  7. #7
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    Re: Check if Cell Is Empty

    Looks more like answer to me...
    Cheers,

    dr

    "Questions, help and advice for free, small projects by donation. large projects by quote"

    http://www.ExcelVBA.joellerabu.com

  8. #8
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Check if Cell Is Empty

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

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Check If Cell is Not Empty & Less Than Today
    By kp_abhilash in forum EXCEL HELP
    Replies: 2
    Last Post: August 16th, 2007, 16:13
  2. Before Paste Check Cell, If Not Empty Use Red
    By pilch in forum EXCEL HELP
    Replies: 2
    Last Post: June 27th, 2007, 02:02
  3. Check If Cell Is Empty Before XlUp
    By pike in forum EXCEL HELP
    Replies: 6
    Last Post: May 19th, 2007, 21:20
  4. Check Empty Cell
    By h in forum EXCEL HELP
    Replies: 3
    Last Post: February 21st, 2007, 05:08
  5. AutoFilter: Check if Cell Empty
    By Vargtass in forum EXCEL HELP
    Replies: 4
    Last Post: October 25th, 2006, 18:41

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno