G
grahamd
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,H2521,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
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,H2521,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