Macro to work out a age range

[leemcder]

Hi, can anyone help me with this please? I have several spreadsheets where I formula the age on year based on the date of an invoice and todays date. I then add a column and add a date range (manually) and work out a age range of 0-1 years, 1-2, 2-3, 3-4,4-5, 5+

So I currently add 2 columns, one with the age and one with age range. Can a macro put in the date range based on the age? example 1.5 years would return 1-2 years, and 2.8 years would return 2-3.

The location of the two cells I add can change so is it also possible the a pop up box can ask me what row the age figure is and also ask what cell I want the age range row?

Many thanks

 

[dfsobral]

you can use vlookup formula to search and return the correct range

to do so you just need create a table like this to look for the correct range

0 0-1 Years
1 1-2 Years
2 2-3 Years
3 3-4 Years
4 4-5 Years
5 +5 Years

 

[leemcder]

Thanks but this wouldn't find 0.1 for example, it needs to be between 0 and 1, and between 1 and 2 etc, this formula works though

=IF(AND($A1>=0,$A1<1),"0>1",IF(AND($A1>=1,$A1<2),"1>2",IF(AND($A1>=2,$A1<3),"2>3",IF(AND($A1>=3,$A1<4),"3>4",IF(AND($A1>=4,$A1<5),"4>5","5+")))))

I just need a VBA which will ask which cell do you want the formula in, I can then get the VBA to copy the formula from the cell the user selected down to the last row of data. I need the user to select which cell to enter the formula because this will be used on several spreadsheets.​

you can use vlookup formula to search and return the correct range

to do so you just need create a table like this to look for the correct range

0 0-1 Years
1 1-2 Years
2 2-3 Years
3 3-4 Years
4 4-5 Years
5 +5 Years

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Age​

[/td][td="bgcolor:#F3F3F3"]

Range​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

0.9​

[/td][td="bgcolor:#CCFFCC"]

0>1​

[/td][td="bgcolor:#CCFFCC"]B1: =LOOKUP(A1, {0,1,2,3,4,5}, {"0>1","1>2","2>3","3>4","4>5","5+"})[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1.1​

[/td][td="bgcolor:#CCFFCC"]

1>2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1.5​

[/td][td="bgcolor:#CCFFCC"]

1>2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

2.3​

[/td][td="bgcolor:#CCFFCC"]

2>3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

3.6​

[/td][td="bgcolor:#CCFFCC"]

3>4​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

4.4​

[/td][td="bgcolor:#CCFFCC"]

4>5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

5.1​

[/td][td="bgcolor:#CCFFCC"]

5+​

[/td][td][/td][/tr]
[/table]