none
Different result for automatic calculation and manual calculations of formula in a cell RRS feed

  • Question

  • I prepared a function by using macro. But this function also includes "if statement" and "for ...next". Everything works fine when i am manually refreshing the cells by choosing the formula  from the cell's content and press enter. However, when i choose automatic refresh, function still works without any error messages but gives different result. While doing step by step control on the macro there is no issue and works fine. But when i do ctrl+alt+F9, it gives wrong result. I doubt that the problem could be the "if statement" in the function. I need your help and assistance on this issue because i am trying to solve this issue for more than 2 weeks and i couldnt get even close to it. Maybe somebody there could solve my problem with their experiences. Thank you.
    Wednesday, October 12, 2016 8:07 AM

All replies

  • If possible pls share code so that we can have a look and try to find a solution

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 12, 2016 8:16 AM
    Answerer
  • This is the function code i used. Thanks in advance.


    Function AYLIK_COST(Cost As Double, start As Date, finish As Date, ay_yil As Date, tatil As Range)

    Dim ay_basi As Date
    Dim ay_sonu As Date

    ay_basi = ay_yil
    ay_sonu = WorksheetFunction.EoMonth(ay_basi, 0)

    tatil_ay = 0
    tatil_tum = 0
    tatil_ay_sonu = 0
    tatil_ay_basi = 0
    tatil_ay_bay = 0
    tatil_tum_bay = 0
    tatil_ay_sonu_bay = 0
    tatil_ay_basi_bay = 0
    cur_row = ActiveCell.Row

    For Each cell In tatil
    celo = CDate(cell)
    If cell.Offset(0, 1).Text = "" And celo >= ay_basi And celo <= ay_sonu Then tatil_ay = tatil_ay + 1
    If cell.Offset(0, 1).Text = "" And celo >= start And celo <= finish Then tatil_tum = tatil_tum + 1
    If cell.Offset(0, 1).Text = "" And celo >= start And celo <= ay_sonu Then tatil_ay_sonu = tatil_ay_sonu + 1
    If cell.Offset(0, 1).Text = "" And celo >= ay_basi And celo <= finish Then tatil_ay_basi = tatil_ay_basi + 1

    If cell.Offset(0, 1).Text = "Bayram" And celo >= ay_basi And celo <= ay_sonu Then tatil_ay_bay = tatil_ay_bay + 1
    If cell.Offset(0, 1).Text = "Bayram" And celo >= start And celo <= finish Then tatil_tum_bay = tatil_tum_bay + 1
    If cell.Offset(0, 1).Text = "Bayram" And celo >= start And celo <= ay_sonu Then tatil_ay_sonu_bay = tatil_ay_sonu_bay + 1
    If cell.Offset(0, 1).Text = "Bayram" And celo >= ay_basi And celo <= finish Then tatil_ay_basi_bay = tatil_ay_basi_bay + 1
    Next

    If cells(ActiveCell.Row, 1).Font.Bold = False And start >= ay_basi And start <= ay_sonu Then gun = ay_sonu - start - tatil_ay_sonu - tatil_ay_sonu_bay + 1
    If cells(ActiveCell.Row, 1).Font.Bold = False And finish >= ay_basi And finish <= ay_sonu Then gun = finish - ay_basi - tatil_ay_basi - tatil_ay_basi_bay + 1
    If cells(ActiveCell.Row, 1).Font.Bold = False And (start >= ay_basi And start <= ay_sonu) And (finish >= ay_basi And finish <= ay_sonu) Then gun = finish - start - tatil_tum - tatil_tum_bay + 1
    If cells(ActiveCell.Row, 1).Font.Bold = False And start < ay_basi And finish > ay_sonu Then gun = ay_sonu - ay_basi + 1 - tatil_ay - tatil_ay_bay

    If cells(ActiveCell.Row, 1).Font.Bold = True And start >= ay_basi And start <= ay_sonu Then gun_bay = ay_sonu - start - tatil_ay_sonu_bay + 1
    If cells(ActiveCell.Row, 1).Font.Bold = True And finish >= ay_basi And finish <= ay_sonu Then gun_bay = finish - ay_basi + 1 - tatil_ay_basi_bay
    If cells(ActiveCell.Row, 1).Font.Bold = True And (start >= ay_basi And start <= ay_sonu) And (finish >= ay_basi And finish <= ay_sonu) Then gun_bay = finish - start + 1 - tatil_tum_bay
    If cells(ActiveCell.Row, 1).Font.Bold = True And start < ay_basi And finish > ay_sonu Then gun_bay = ay_sonu - ay_basi + 1 - tatil_ay_bay


    If cells(ActiveCell.Row, 1).Font.Bold = False Then
    AYLIK_COST = Round((gun / ((finish - start) + 1 - tatil_tum - tatil_tum_bay)) * Cost, 2)
    End If

    If cells(ActiveCell.Row, 1).Font.Bold = True Then
    AYLIK_COST = Round((gun_bay / ((finish - start) + 1 - tatil_tum_bay)) * Cost, 2)
    End If

    End Function


    Friday, October 14, 2016 12:14 PM
  •  

    Hi,

    In your function, it seems you are comparing the date and calculate a value.

    Could you share a sample formula to use the function?

    I am not sure how to use your function.

    "start" is the start date and "finish" is end date,"ay_sonu" is the last day of the very month

    What is "ay_basi/ay_yil" in your function? How should we we set "tatil"?

     

    Besides, How do you "automatic refresh"  you mentioned in the original post?

    If possible, I suggest you share with us a sample document with some available data.

    You could upload the document in the OneDrive and paste the URL here.

    Thanks for your understanding.

    Regards,

    Celeste

    Monday, October 17, 2016 8:30 AM
    Moderator
  • Sorry for late response. I uploaded my file to Wetransfer. Here is the link  

    https://www.wetransfer.com/downloads/5ab90abbc1c1248af73b1a969d3afecd20161101061009/e5ea0562e81edb63a423362ace3ea87920161101061009/441801

    You can now see how it works. It was first in automatic calculation and was doing update on every changes on table. But i realized that it was giving wrong results. When i did step by step control on my macro, i saw that results were correct. Then i decided to turn off the automatic update and use the manual update. Now i am choosing the address section of a cell and just click " enter". This manually updates the cell and gives the correct result. But there are lots of cells and it takes time doing all these updates manually.  Thank you for all your kind assistance.

    Hakan


    • Edited by quarismax Tuesday, November 1, 2016 6:24 AM
    Tuesday, November 1, 2016 6:23 AM