Convert number into aging/bucketing by creating FUNCTION in VBA.Enable FUNCTION to work in all files
CONVERT NUMBERS INTO AGING / BUCKETING BY CREATING FUNCTION IN EXCEL VBA. For example :- 6 INTO a bucket of 0-30 or 35 into a bucket 31-60
Follow the below given steps to create your own private function to convert a number into aging
/ bucketing :-
Step 1. Copy the below VBA Function
Step 2. Create a new sheet, go to VBA editor and insert a new module
Step 3. Paste the copied function in the module
Step 4. Change the bold text given in the all lines with greater than or less than symbol as per requirement
Step 5. Test the function (currently it is available in the current active workbook only)
Step 6. To enable the function to work on all the workbooks, save as your file with excel file type Excel Add - in (.XLAM)
Step 7. Click on file menu and go to the option, then select Add - Ins, after selecting add-ins, you will find multiple function available there, select the Aging Function and then click on the Go button
Step 8. A pop up windows will option with few function, you need to selection the Aging Function and then click ok
Your Function is ready to use in all workbooks / Excel files
Function aging(cells As Integer, cells1, cells2, cells3, cells4, cells5 As Integer) As String
Select Case cells
Case Is [is less than] = cells1
aging = cells1 - cells1 & " - " & cells1
Case Is [is less than]= cells2
aging = cells1 + 1 & " - " & cells2
Case Is [is less than]= cells3
aging = cells2 + 1 & " - " & cells3
Case Is [is less than]= cells4
aging = cells3 + 1 & " - " & cells4
Case Is [is less than]= cells5
aging = cells4 + 1 & " - " & cells5
Case Is [is greater than]= cells5
aging = "greater than " & cells5
End Select
End Function
Link of My Videos
Create your private Function using VBA Editor
• Convert date in Financial Year using Funct...
How to create a personal workbook so that a macro can run on any excel files without saving as macro enabled workbook
• Run Macro on any excel file by just storin...
Consolidate multiple workbooks / excel files into one excel workbook
• Consolidate / Merge Multiple workbooks / s...
Copy files and folder names using command prompt
• Copy all files and folders name in text fi...
Copy files and folder name in an excel file using VBA Excel MACROS
• Copy file names and folder names using VBA...
Count duplicate value using if and sort function in a very large excel data
• Count duplicate values using IF Function w...
Top 5 shortcut key used in Excel
• Most Commonly/frequently used top five exc...
Count duplicate value using countIF Function
• Count duplicate values using countIF funct...
How to split one single big file into multiple small files in size so to enable to send it through email
• How to SPLIT a big file of any format usi...
How to reduce file size even better than zip filing using zipx option of WINZIP
• Reduce file size by using WINZIP advance O...
How to merge multiple sheets into one excel sheet
• VBA Macro to merge/ console multiple Sheet...
How to delete a sheet using MACRO
• VBA Macro to delete Sheet in a workbook...
How to create a Macro to add a new sheet automatically
• Macro to add / create a new sheet in an ex...
How to create a password protected excel file
• How to create password protected excel fil...
Create your first Macro in Excel VBA
• My First Excel VBA Macro program. How to w...
How to use IFERROR Function with Vlookup Function
• IFERROR with Vlookup function is used to r...
Use of Hlookup Function
• Use of HLOOKUP FUNCTION - HLOOKUP FUNCTION...
Use of Vlookup Function
• VLOOKUP FUNCTION - Use VLOOKUP when you ne...
Use of Lookup Function
• LOOKUP FUNCTION -A value that LOOKUP searc...
Create multiple files from a single file based on a criteria using macros in excel VBA
• Use of Macro to Un-merge / Split data from...
MID Function - Extract number / words from a dynamic position / position not fixed
• MID Function - Extract number / words from...
Change date format using Text Function
• Change date format using Text Function
How to protect rows or columns - Allow edit range allows you to edit only selected ranges in Excel
• How to protect rows or columns - Allow ed...
Concatenate function used with text function, joins two or more text / numbers into one
• Concatenate function used with text functi...
Text Function - change the way a number /text / date appears
• Text Function - change the way a number /t...
How to use Subtotal function to count visible cells only in a given range
• How to use Subtotal function to count visi...
Use of Subtotal function to sum / total of all visible cell in a given range
• Use of Subtotal function to sum / total of...
Use of Dollar Sign/ How to use $ Sign, Freeze rows or column and both
• Use of Dollar Sign/ How to use $ Sign, Fre...
How to console / Merge multiple text files into one text file using command prompt / DOS / CMD
• How to console / Merge multiple text files...
How to use SUMIFS function, sums a selected range with multiple criteria's / conditions
• How to use SUMIFS function, sums a selecte...
How to create aging / Bucketing using IF Function in excel
• How to create aging / Bucketing using IF F...
SUMIF Function - Sumif function, sums up a range with a single condition / Criteria
• How to use SUMIFS function, sums a selecte...
How to create aging / Bucketing using IF Function in excel
• How to create aging / Bucketing using IF F...
SUMIF Function - Sumif function, sums up a range with a single condition / Criteria
• SUMIF Function - Sumif function, sums up a...
Use of Countif Function - How to count cells in a range with a condition / Criteria
• Use of Countif Function - How to count ce...