Macro to work out a age range

leemcder

New Member
Joined
Feb 26, 2018
Messages
42
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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
 
Upvote 0
A​
B​
C​
1​
Age
Range
2​
0.9​
0>1​
B1: =LOOKUP(A1, {0,1,2,3,4,5}, {"0>1","1>2","2>3","3>4","4>5","5+"})
3​
1.1​
1>2​
4​
1.5​
1>2​
5​
2.3​
2>3​
6​
3.6​
3>4​
7​
4.4​
4>5​
8​
5.1​
5+​
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top