Macros Formula Creates Error in Entire Column

[chiepet]

Hello. So I've been stuck with this problem for quite some time now. I created a formula using macros and applied it to an entire column in my spreadsheet. However, once I edit the value of some other cell the macros references to OR delete a row anywhere in the sheet, all the cells that use that macros turns into 0 or #value!. But once I click through the cells in the column and press enter or do F2+Enter it returns to its correct value.

I've checked the settings so the file automatically calculates every time the spreadsheet is updated, I've tried "Text To Columns" and updating the entire column, I've tried Ctrl+Alt+F9, I've also copied everything and created a new doc in case the older one was corrupted or something but none of these methods give me the right solution.
I'd appreciate any sort of help at this point. Thanks.

 

[Smitty]

Welcome to the Board!

How about posting the code/formula(s) in question?

 

[chiepet]

Thank you Smitty!

My code is here:

Function TotalAvg(Rate As Double, Cell As String) As Double
Dim iRow As Integer, numValues As Integer
Dim numTotal As Currency, Total As Currency

iRow = ActiveCell.Row
numValues = 1
numTotal = 1
Do Until (Cells(iRow + 1, 3).Value <> 0)
iRow = iRow + 1
numTotal = numTotal + 1
Loop
Total = Cells(iRow, 2).Value
Do While (numTotal > 1 And DateDiff("yyyy", Cells(iRow, 4).Value, Cells(iRow - 1, 4).Value) = 0)
numValues = numValues + 1
Total = Total + Cells(iRow - 1, 2).Value
iRow = iRow - 1
Loop
TotalAvg = Total / numValues
End Function