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!

    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

    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.

    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!

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


    Quote

    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

    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?

    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

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


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

    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 [COLOR="Red"]Format > Cell >
    Protection[/COLOR] unchecked locked and then went to [COLOR="Red"]Tools > Protection > Protect Sheet[/COLOR] and unchecked select locked cells and left check mark on select unlocked cells.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login