My problem was as follows

Id written a function in VBA as below (calcHigherAgeRate)it takes in
the parameters and attempts to find a match in the next 9 rows from
the current active row


However when i filled my formula down for all 2520 rows
i.e.
in cell
"L2" =calcHigherAgeRate(A2,B2,C2,D2,F2,H2,I2)
TO
"L2521" =calcHigherAgeRate(A2521,B2521,C2521,D2521,F2521,H 2521,I2521)



it was as if the formula couldnt keep up - it would calculate the
first 7 or so cells then set the rest to 0.
However if the user was to navigate to one of the other cells say
"L101" press F2 and hit return the cell would be calculated

As a hacked solution i wrote the following Sub
'================================================= ========================
' Sub: X
'
' Description: calls calcHigherAgeRate for each cell in the range
'
' i.e. column L as mentioned above
'
'================================================= ========================
Sub X()

For i = 2 To Range("HigherAgeRate1").Rows.Count

Range("HigherAgeRate1")(i).FormulaR1C1 = _
"=calcHigherAgeRate(RC[-11],RC[-10],RC[-9],RC[-8],RC[-6],RC[-4],RC-3])"

Next i
End Sub


This didnt improve things!!

But by adding the line

Range("HigherAgeRate1")(i).Select

just before the line "Next i"

It seems as if this slows excel up and stops it running ahead of
itself... Can anyone give me a slicker solution that i could add to my
function calcHigherAgeRate which will keep things in check



'================================================= ========================
' Function: calcHigherAgeRate
'
' Description: works out the next age rate
'
'================================================= ========================
Function calcHigherAgeRate(ByVal Life1 As String, _
ByVal Life1LowerAge As Integer, _
ByVal Life2 As String, _
ByVal Life2LowerAge As String, _
ByVal RevGtee As Integer, _
ByVal Esc As Integer, _
ByVal Gtee As Integer) As Double

For i = ActiveCell.Row + 1 To ActiveCell.Row + 9
If (Range("incomes").Cells(i, 1).Text = Life1 And _
Range("incomes").Cells(i, 2).Value = Life1LowerAge
+ 5 And _
Range("incomes").Cells(i, 3).Text = Life2 And _
Range("incomes").Cells(i, 4).Value = Life2LowerAge
And _
Range("incomes").Cells(i, 6).Text = RevGtee And _
Range("incomes").Cells(i, 8).Value = Esc And _
Range("incomes").Cells(i, 9).Value = Gtee) Then

calcHigherAgeRate = Range("incomes").Cells(i, 11).Value
Exit Function
End If
Next i
End Function

PS
(I know referencing other cells from within my function is not good
practice but i could see no other way out of it)



Heres my hacked Sub X() with the holding manouvre
'================================================= ========================
' Sub: X
'
' Description: calls calcHigherAgeRate for each cell in the range
'
' i.e. column L as mentioned above
'
'================================================= ========================
Sub X()

For i = 2 To Range("HigherAgeRate1").Rows.Count
Range("HigherAgeRate1")(i).FormulaR1C1 = _
"=calcHigherAgeRate(RC[-11],RC[-10],RC[-9],RC[-8],RC[-6],RC[-4],RC-3])"

Range("HigherAgeRate1")(i).Select

Next i
End Sub