Give remarks based on Age calculation

[saravanan1981]

I have data with Name, Date of birth and Age as per format. I need vba code to check the age of the persons.

Need common Remarks in Column O

I am using some code but that is giving all desired results. code given at the end of this page

Criteria is

if Self OR Spouce OR Father OR Mother OR Father-in-law OR Mother-in-Law (As per Relation mentioned "in Column I) age is less than 18 years (As per age mentioned "in Column K) then need result as "Self age is less than 18 years" OR "Spouce age is less than 18 years" OR “Father age is less than 18 years” and so on in Column O against all family members of particular family code. E.g. Refer column No.O2 to O5.

if Self OR Spouce OR Father OR Mother OR Father-in-law OR Mother-in-Law (As per Relation mentioned "in Column I) age is above 80 years (As per age mentioned "in Column K) then need result as "Self age is above 80 years" OR "Spouce age is above 80 years" OR “Father age is above 80 years” and so on in Column O against all family members of particular family code. E.g. Refer column No.O24 to O25.

if any Child (Child, Child2, Child3,…. age is above 25 years (As per age mentioned "in Column K) then need result as "Child age is above 25 years "in Column O. For other child "Child2 age is above 25 years OR Child3 age is above 25 years" E.g. Refer column No.O7 to O10.

Also Need Some Logical Remarks in Column O


If age difference between Self and Spouce is more than 15 years then need result as "Self and Spouce age difference is more than 15 years "in Column O.

If age difference between Self and Child, Child2, Child3….. is less than 18 years then need result as "Self and Child age difference is less than 18 years "in Column O.

If age difference between Self and Father is less than 18 years then need result as "Self and Father age difference is less than 18 years "in Column O.

If age difference between Self and Mother is less than 18 years then need result as "Self and Mother age difference is less than 18 years "in Column O.

If age difference between Self and Father-in-law is less than 18 years then need result as "Self and Father-in-law age difference is less than 18 years "in Column O.

If age difference between Self and Mother-in-law is less than 18 years then need result as "Self and Mother-in-law age difference is less than 18 years "in Column O.

If age difference between Spouce and Child, Child2, Child3….. is less than 18 years then need result as "Spouce and Child age difference is less than 18 years "in Column O.

If age difference between Spouce and Father is less than 15 years then need result as "Spouce and Father age difference is less than 15 years "in Column O.

If age difference between Spouce and Mother is less than 15 years then need result as "Spouce and Mother age difference is less than 15 years "in Column O.

If age difference between Spouce and Father-in-law is less than 18 years then need result as "Spouce and Father-in-law age difference is less than 18 years "in Column O.

If age difference between Spouce and Mother-in-law is less than 18 years then need result as "Spouce and Mother-in-law age difference is less than 18 years "in Column O.

If any cell in Age (Column I) contains following


#NUM! Wrong Age Mentioned
#N/A Wrong Age Mentioned
#NULL! Wrong Age Mentioned
#REF! Wrong Age Mentioned
#DIV/0! Wrong Age Mentioned
##### Wrong Age Mentioned
Errors Wrong Age Mentioned
OR any kind of error Wrong Age Mentioned

please note: very important is if there is multiple remarks applicable to any family All the remarks should be there separated by “/” against all members of that particular family. For example refer cell O16 to O20 and cell O26 to O28

This is very important for me. Please help me with code.

Please Login or Register  to view this content.

[pjwhitfield]

I have a quick question that isnt directly linked to your request but will help to determine how the answers work.

1. How do you identify a "family" as such? at the moment its just a list of Relationships one after the other, they seem to be grouped in colours (which i assume is manual), is it always the case that the records will start with the "Self" item? and therefore that can be used to determine relevant records?

EDIT: Rows 4 & 5 of the example show an error of "under 18" yet both records are 30? is this just an error in the example or am I missing something?

[saravanan1981]

oops. sorry,

You can consider family number given in column P.

[saravanan1981]

yes row number 4 and 4 is just an error

[saravanan1981]

Hey Sorry,

row Number four and five is not an error.

It Shows "Self age is less than 18 years"
Because in that particular family self age is <18 years. Hence it is mentioned against all members of the family.

[pjwhitfield]

How is it possible though to have two "Self" and "Spouse" entries in the same family?

[saravanan1981]

Dear Sir,
please find attached revised file for your reference.