Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula result a 0 instead of TRUE (2002)

    I have the following formula:

    =IF(trans_opt=1,AND(starty<=B4,finishy>=B4,(INT((B 4-starty)/yspace))=(B4-starty)/yspace),AND(startx<=A4,finishx>=A4,(INT((A4-startx)/xspace))=(A4-startx)/xspace))

    It checks a number in trans_opt (it is 1 or 2), based on that it performs any of 2 AND functions. It should return a TRUE or FALSE value.

    The problem is that no matter what the input cells, it always returns a "0". I have checked every term of the equation by going to cell edit mode, selecting and pressing F9 checking for partial results, and know when the formula should return FALSE or TRUE.

    And the strangest thing is that the formula used to display the result as FALSE or TRUE until today, when I started editing it to modify it. Result as TRUE or FALSE worked before!

    The cell that has the formula is formated as "General". But I havent changed the formating since I started. The only thing that happened , was that several times I had errors with the parenthesis and received the syntax error message and a couple of times accepted the suggested solution. Other than that, I cant think of any other anomalous thing.

    Help please!
    Guillermo

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula result a 0 instead of TRUE (2002)

    Check the following:
    <UL><LI>Select Tools | Options...
    <LI>Activate the Transition tab.
    <LI>Make sure that the check box labeled "Transition formula evaluation" in the "Sheet options" section is cleared.
    <LI>Click OK.[/list](If the check box was clear already, check it, then click OK, open the Options dialog again, clear the check box and click OK)

    Does this help?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula result a 0 instead of TRUE (2002)

    I am sorry about this post.

    When I went back to my formulas and macros, something clicked in my head...

    The problem is related to a macro that disables recalculation of the spreadsheet:

    Private Sub setnewparam_Click()
    '
    Worksheets("gpft").EnableCalculation = False
    '
    Dim userinput As Integer
    '
    userinput = newmaxx.Value
    Sheets("hidden_data").Range("finishx").Value = userinput
    '
    Worksheets("gpft").Calculate
    '
    End Sub

    I didnt realize that by executing this macro I was turning recalc off.

    I solved with adding at the end of the sub:

    Worksheets("gpft").EnableCalculation = True
    Worksheets("gpft").Calculate

    Does this sound reasonable?
    Guillermo

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula result a 0 instead of TRUE (2002)

    I don't understand why turning off calculation would cause a boolean (TRUE/FALSE) value to be displayed as a number, but it certainly will result in the displayed result being incorrect, or at least suspect.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula result a 0 instead of TRUE (2002)

    I am not sure.

    I thought that F9 would recalc the spreadsheet. So in the process, I tried recalculating with F9 several times. But obviously ...

    Worksheets("gpft").EnableCalculation = False

    cannot be overiden with F9.

    I aslo had several delete and copy and paste on the cell containing the formula, with the recalc disabled. Maybe that has something to do with the "0" displayed.
    Guillermo

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula result a 0 instead of TRUE (2002)

    VBA help says about EnableCalculation "When the value of this property is False, you cannot request a recalculation." Perhaps you were thinking about the effect of Application.Calculation = xlManual. This will prevent automatic calculation, but still allows recalculation by pressing F9.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Houston, Tx, USA
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula result a 0 instead of TRUE (2002)

    excellent observation and help!
    Guillermo

Posting Permissions

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