Max age with vba

[prlondhe]

I AM TRYING TO FIND THE MAX AGE FOR A PARTICULAR VALUE WITH THE FOLLOWING VBA CODE

THE SHEET STRUCTURE IS AS BELOW

CLIENT CODE

SERIAL NUMBER

CLIENT NAME

COMPANY CODE

POLICY TYPE

POLICY SUB TYPE

VEHICLE NUMBER

DATE OF BIRTH

SUM INSURED

RES. TELEPHONE

OFF. TELEPHONE

MOBILE

ADDRESS

PAN CARD NUMBER

PASSPORT NUMBER

<tbody> </tbody>

<tbody>
</tbody>

Sub MAXAGE()


Sheets("MASTER").Select
Range("A2").Select
Do While ActiveCell.Value <> ""
CD = ActiveCell.Value


If ActiveCell.Offset(0, 3).Value = "NIA" And ActiveCell.Offset(0, 7).Value <> "" Then
Range("AC2").FormulaArray = Evaluate("=MAX(IF(A:A=CD,p:P))")
ActiveCell.Offset(0, 16).Value = Range("AC2").Value
End If



Sheets("MASTER").Select
ActiveCell.Offset(1, 0).Select
Loop


End Sub

GETTING RESULT AS #NAME?

CAN ANYBODY HELP

 

[steve the fish]

I haven't read all of it but the reason you are getting name error is that the way its written CD will be thought by excel to be a named range. You need to concatenate variables and you also don't need the formulaarray part:

Range("AC2") = Evaluate("=MAX(IF(A:A=" & cd & ",P:P))")

if cd is a number

or

Range("AC2") = Evaluate("=MAX(IF(A:A=""" & cd & """,P:P))")

if text

 

[Teeroy]

I think that there may be a couple of problems.

Since you are writing a formula to a cell you shouldn't be wrapping it in the Evaluate function.

Your If statement can't be evaluated so it won't return a number for the Max function.

It's not clear from your code exactly what group you are after the Max Age of. Can you create the formula in the spread sheet and post that to help determine exactly what you are trying to achieve.

 

[prlondhe]

Thank you very much for your prompt reply