Announcement

Collapse
No announcement yet.

If No Data Leave It Blank (cell Contains Formula)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • If No Data Leave It Blank (cell Contains Formula)



    Hi,

    A1 has a date of 03/10/07
    B2 has formula =A1 + 7
    I want to copy that formula down the column B2 but if
    B3 is empty I get 01/07/1900
    How Do I make so that if there's no date entered in
    A column I get blank cells but still retain formulas,
    I've been running into this issue with other formulas but I couldn't find any solution searching this forum,

    How do you call this type of problem ?

    Thanks!

  • #2
    Re: If No Data Leave It Blank (cell Contains Formula)

    This Formula would work
    =IF(A1="","",A1+7)
    I would prefer the Before Double_Click instead
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        If Target.Column <> 2 Then Exit Sub
        If Target.Offset(0, -1) = "" Then
            Cancel = True
            Exit Sub
        Else: Target = Target.Offset(0, -1) + 7
        End If
        Cancel = True
    End Sub
    Or you can use a WorkSheet Change event
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        If Target.Column <> 1 Then Exit Sub
        If Application.WorksheetFunction.IsNumber(Target) = True Then Target.Offset(0, 1) = Target + 7
    End Sub
    lenze
    Last edited by royUK; April 1st, 2007, 01:43.

    Comment


    • #3
      Re: If No Data Leave It Blank (cell Contains Formula)

      I don't understand. Is this manual copying or in VBA?

      If manual, then does B2 have to still refer to A1 or to A2? And how can B3 be empty if you are copying the formula down into it? Perhaps you could attach a sample workbook with the problem.

      Welcome to the forum.
      Last edited by ByTheCringe2; March 31st, 2007, 04:24.
      .

      Comment


      • #4
        Re: If No Data Leave It Blank (cell Contains Formula)

        First of all thanks for a warm welcome!

        this solves my problem =If(A1="","",A1+7)
        Since I'm new to vba when I get home I'll try to figure out where to enter the other two codes and test how it works,
        attached is test spreadsheet in sheet1 is my problem in sheet2 problem is solved using above formula,
        I just need to figure out how to incorporate above in my other formulas,

        Thanks!
        Attached Files

        Comment


        • #5
          Re: If No Data Leave It Blank (cell Contains Formula)

          Since I'm new to vba when I get home I'll try to figure out where to enter the other two codes and test how it works
          Both codes would go in the WorkSheet module. Right Click on sheet tab and choose "View Code"

          lenze

          Comment


          • #6
            Re: If No Data Leave It Blank (cell Contains Formula)

            lenze, please don't use Code tags for formulas
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Re: If No Data Leave It Blank (cell Contains Formula)

              Thanks lenze!

              you saved me a lot of time, I didn't check my email so I didn't see your reply and I was trying everything but your tip

              I tried your Double_Click but that only forbids double click of selected cell and changing value, second one WorkSheet Change event I couldn't figure out what it does
              anyway, your first suggestion works perfect =If(A1="","",A1+7) with that I'm able to copy formula down the column without 01/07/1900 appearing in every copied cell,

              I was able to implement your fix into my other formula = TODAY()-A2 and it works perfect again =IF(A3="","",TODAY()-A3)

              but I can't implement into this
              =IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times")

              any idea guys?

              Comment


              • #8
                Re: If No Data Leave It Blank (cell Contains Formula)

                Try:

                =IF((B2="","",IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times"))

                EDIT: Contains an error, see correction below.
                Last edited by ByTheCringe2; April 1st, 2007, 05:32.
                .

                Comment


                • #9
                  Re: If No Data Leave It Blank (cell Contains Formula)

                  I tried and I got
                  "The formula you typed contains an error"
                  for information about fixing common.......

                  B2 contains date 03/31/2007 and is getting that date from this formula =A2 + 30
                  Last edited by JohnnyBeGood; April 1st, 2007, 05:18.

                  Comment


                  • #10
                    Re: If No Data Leave It Blank (cell Contains Formula)

                    Sorry, try:

                    =IF(B2="","",IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times"))
                    .

                    Comment


                    • #11
                      Re: If No Data Leave It Blank (cell Contains Formula)

                      Originally posted by ByTheCringe2
                      Sorry, try:

                      =IF(B2="","",IF(B2>TODAY(),B2-TODAY(),"One time "&TODAY()-B2&" Times"))
                      Thanks!
                      it works perfect.
                      problem solved : D

                      One more question,
                      can I use lenze's code to protect column D, E and F from users trying to alter the outputs of formula's?
                      and leave column A, B and C for users to change,
                      Code:
                      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
                          If Target.Column <> 2 Then Exit Sub 
                          If Target.Offset(0, -1) = "" Then 
                              Cancel = True 
                              Exit Sub 
                          Else: Target = Target.Offset(0, -1) + 7 
                          End If 
                          Cancel = True 
                      End Sub
                      I tried to alter it my self but couldn't figure out where in the code I can above columns.
                      Last edited by JohnnyBeGood; April 1st, 2007, 08:39.

                      Comment


                      • #12
                        Re: If No Data Leave It Blank (cell Contains Formula)

                        Ok, finally accomplished what I wanted
                        maybe someone will find it useful in the future,

                        to lock certain columns and leave others editable (in my example I wanted to leave A, B and C editable)
                        I selected column A, B and C and then went to Format > Cell >
                        Protection
                        unchecked locked and then went to Tools > Protection > Protect Sheet and unchecked select locked cells and left check mark on select unlocked cells.

                        Comment


                        • #13


                          Re: If No Data Leave It Blank (cell Contains Formula)

                          Thank u Lenze.

                          Comment

                          Working...
                          X