In Microsoft Access, using only queries and macros (not VBA) can not make you go far enough. A professional database requires certain calculations and user interface to make it versatile and user-friendly. Access VBA (Visual Basic Application) is the final frontier that worth the effort to learn and master. The payoff can be beyond your … Read more Use Array to read dat from and write data to Tables and Queries
Sometimes I got the below data format when I downloaded data from a system or obtained a file from our clients. The clients might have used a pivot table to create such data and copy-and-paste-value the data before they sent us the file. That poses a problem if I want to further analyze the data … Read more Fill Data in Multiple Empty Cells
A “paper-less” office has been advocated for many years but you probably still see many people are printing tons of paper every day. Many people are still more accustomed to see things on papers rather than on computer monitors. It is also more convenient to use paper reports in a meeting. Many people like to … Read more Use Excel VBA to automate the printing
We can use the “Find & Select” function in Excel to copy only displayed cells after filtering. The same process can also avoid copying cells in the hidden columns / rows. For example, the below data set is filtered to only showing the “Jacket” and “Shoes” under the “Type” field. I want to copy only … Read more Select Displayed Cells for Copying
The Excel file includes two types of collateral: Fixed and Adjustable. The fixed rate mortgage is the more popular one on the market. It is simple and predictable and is well accepted by the borrowers. The Adjustable Rate Mortgage (abbreviated as “ARM”) is less popular but it serves good purpose if you expect the interest … Read more Mortgage Amortization Schedule
If you need to generate documents in a foreign language, you need to install a language pack and add a keyboard input method. Please follow the below steps to do so. Step 1: Click “Start” button and choose “Control Panel”. Step 2: On the Control Panel pop-up screen, choose “Clock, Language, and Region”, and then … Read more Install a Language Pack and Add a keyboard input method
There are several ways of using looping in Excel. In general, there are two main loop mechanisms in Excel VBA: the FOR loop and the DO loop. Each mechanism can be further structured in different ways to make it work as desired. There is no inherent superiority of one loop mechanism over another. The users … Read more Different Looping Mechanisms in Excel VBA
It becomes very easy to create organization chart in the later versions of Microsoft Office Suite. You can do it in Word, Excel, or PowerPoint by using the SmartArt features. Most people do it in PowerPoint for presentation purposes, but you can follow the same steps and use the same commands in Word or Excel. … Read more Create Organization Chart by using SmartArt
Creating a query in Microsoft Access is simple and most people can learn to do it in a relatively short period of time. However, using query in Microsoft Access has two major drawbacks: If you want to create a parameter query and the parameters are saved in a table, it will be very slow to … Read more Use Embedded SQL Query for better efficiency and security
I seldom buy lottery ticket, no matter whether it is Power Ball, Mega Million, or Texas Lotto. I buy them by the motivation of “fear”. I usually buy them with my colleagues when the jackpot accumulates to certain large amount and one of my co-workers will start asking around to have people pooled money to … Read more Lottery Numbers Generator