Asked by:
Different result for automatic calculation and manual calculations of formula in a cell
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.
All replies


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

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

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