When it comes to making lists of records and tables, retention counts, expenses, percentage calculations, averages, financial statements, and more, the best spreadsheet for these tasks is Microsoft Excel. A program known to all in the fields of labor, finance and economics, and education.
You can download Excel from its official website or use the online version, or, if you prefer, you can buy a digital CD to install the full Microsoft Office package, even a mobile version. In this article we will teach you about fOiling makes the automatic counter.
How to create automatic counters using Macros?
This Macros for use with VBA o Visual Basic for Excel, they are the most powerful tools in the Excel program, by using code, we can write specific functions to perform certain actions. While using normal formulas we will be able to calculate VAT totals and subtotals, we will be able to use macros and VBA to enhance this functionality if we wish.
Automatic counter, if we want this way, we will have to use code and write corresponding macros to achieve it. To do this we open the spreadsheet as usual and go ahead and click «Save As» where we choose the path and most importantly, the type of spreadsheet i.e. «Enable Excel Workbook for Macros» with a .xlsm extension .
We go ahead and open the newly saved job again and openExcel Visual Basic Functions This is done using the keyboard shortcut Alt + F11. In the new dialog, we will enter the following code:
- PrivateSubWorkbook_Open()
- response = MsgBox(«Do you want to auto increment?», vbYesNo)
- If response = vbYes Then Range(«B9»).Value = Range(«B9»).Value + 1 End If
- ThisWorkbook.Save
- end sub
What is the way to create a data collector in a spreadsheet?
If we want a list, for example, to make a statement of financial results, we always have to enter new data and update it, which can also be done using macros and VBA. While this accumulator can be done manually, the truth is that it would take too much time and it’s better to do it with just one click.
In principle we do the same as in the previous section, before entering the code, this will obviously be different.We save the macro-enabled spreadsheet, open it again, and Press the appropriate keyboard shortcut Open Excel VBA, go ahead and create the buttons in the style you want before entering the code in the spreadsheet, and when you do that now, we’ll cover the following programming:
Sub insert () Criteria (First Name, Last Name, Account Number, etc.) = Range(«») . Value Criteria 2(First Name, Last Name, Account Number, etc.) = Range(«»). Value Criteria 3 (First Name, Last Name, Account Number, etc.) = Range («»). Value table («Test»). Select row («6:6»). Select Selection.Insert Shift := xlDown Range («») = Criteria Range («») = Criteria End Sub
After inserting the code, go ahead and save the changes, the button should already be enabled Enter new data Accumulate one after the other, without replacement.
How to build an automatic day counter in Microsoft Excel?
The code used in this case will be the same as the code we mentioned in the first section of this article, with a few modifications, since the code we gave will ask you if you want to increment, it’s actually a semi-automatic counter. Instead, automate it so that whenever you go into the spreadsheet every day, The counter is updated without prompting.
First, you have to make your counters according to your style and the way you want, remember you can even move columns around in Excel to organize it better.When you’ve done all the aesthetic parts, now Save your spreadsheet with the extension .xlsm, i.e. enable macros; then open your job again and press Alt+F11 to enable VBA and enter the following code:
- PrivateSubWorkbook_Open()
- Range(«B9»).Value = Range(«B9»).Value + 1 ThisWorkbook.Save
- end sub
How to create a word counter in an Excel sheet?
A sort of word counter It does not require code like in the previous example, it is enough to use 3 formulas, they are =LENGTH(Cell), =SUBSTITUTE(Cell; «»; «»), =LENGTH(Cell). You can use it alone, or if you prefer, use a combination of three to shorten the work and in the same cell to avoid losing organization, leaving something like this: =LONG(SPACE(A1))-LONG(SUBSTITUTE( SPACE( A1);» «;»»))+1
What should you do to create an invoice with consecutive numbers in Excel?
In this case, we can use the same code given in the data counter section, additionally, using the formula =MAX, which we will enter in any box in the spreadsheet, every time we enter new data in the invoice, The formula will generate a continuous unique code for each new data.
