Forum Discussion

kobus1305's avatar
kobus1305
Brass Contributor
Nov 02, 2020
Solved

Keep a Value

I may be this is a stupid question but i am sitting and thinking is it all possible. We all know how if statement works. Logical test, if test is true, if test is false!! Is it possible that, if test is false, keep the value that was in the cell were the if statement is used?? 

excel
  • NikolinoDE's avatar
    NikolinoDE
    Nov 02, 2020

    kobus1305 

     

    Something like that, in that direction?

    If cell A2 contains the text "x"; then enter the value 9 in cell A1; otherwise "leave everything as it is"

    VBA code:

     

    Private Sub Worksheet_Change (ByVal Target As Range)
If Target.Address = "$ A $ 2" Then
If LCase (Range ("A2")) = "x" Then
Application.EnableEvents = False
Range ("A1") = 9
Application.EnableEvents = True
End If
End If
End Sub

     

     or in formula?

    =IF(A2="x",9,A1)

     

    Hope I was able to help you.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

8 Replies

  • kobus1305's avatar
    kobus1305
    Brass Contributor
    Nov 02, 2020
    Hi, For more clearity this is what i am trying to do. =IF(and($A$1<>0,A4<>0,B4<>0),TODAY(),if(and($A$1=0,A4<>0,B4<>0),Know I do not want to change the date to today, it may be a later date, this Cell is D4 and it must keep the date that was in there. Thanks for your patience with me!!!!! Thank You Regards
    • mtarler's avatar
      mtarler
      Silver Contributor
      Nov 02, 2020

      kobus1305 a cell can only hold a Value OR a Formula.  If you put that formula in the cell and the use types a date in that cell it will overwrite/erase the formula in that cell.  You need to create a helper column so for example column D is Entered Date and column E has the formula.

      • kobus1305's avatar
        kobus1305
        Brass Contributor
        Nov 02, 2020

        mtarler 

        Hi,

        Thank you this is what i understood. =IF(AND($A$1<>0,A4<>0,B4<>0),D4=TODAY(),"") this formula is in E4 and i want the today() in D4 only if conditions is correct. Is that what you meant? The D4=Today() do not enter the today() in D4???

        Thank You

        Regards

         

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 02, 2020

    kobus1305 

     

    Something like that, in that direction?

    If cell A2 contains the text "x"; then enter the value 9 in cell A1; otherwise "leave everything as it is"

    VBA code:

     

    Private Sub Worksheet_Change (ByVal Target As Range)
If Target.Address = "$ A $ 2" Then
If LCase (Range ("A2")) = "x" Then
Application.EnableEvents = False
Range ("A1") = 9
Application.EnableEvents = True
End If
End If
End Sub

     

     or in formula?

    =IF(A2="x",9,A1)

     

    Hope I was able to help you.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here