Announcement

Collapse
No announcement yet.

Check if Cell Is Empty

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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.


    Code:
    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
    Cheers,

    dr

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

    http://www.ExcelVBA.joellerabu.com

    Comment


    • #3
      Re: Vb Treats 0 As Empty Too

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

      Comment


      • #4
        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

        Comment


        • #5
          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....)

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment


              • #8


                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

                Comment

                Working...
                X