# 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.
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,

Wednesday, October 12, 2016 8:16 AM
• 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.

Regards,

Celeste

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