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

The only way Excel can be aware of which cells have to be recalculated, is
to include all input for the function in the argument list of the function
call (which means, in the argument list of the function definition too).
So change your function to supply the 9 rows as arguments.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"grahamd" wrote in message
om...
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

Many thanks Niek


heres what i did i called my function as follows

calcHigherAgeRateGoogle(A9,B9,C9,D9,F9,H9,I9,A9:I1 6)

and the function was rewritten as follows

================================================== ======
' Function: calcHigherAgeRateGoogle
'
' Description: works out the next age rate based on
' suggestion from google groups
'
'================================================= ======
Function calcHigherAgeRateGoogle(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, _
ByVal lookUpRange As Range) As Double


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

calcHigherAgeRateGoogle = lookUpRange.Cells(i, 11).Value

Exit Function
End If
Next i
End Function

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!