Excel VBA Application.OnTime Method to schedule event with timer

Posted on April 4, 2015July 19, 2016 by Wyman W

This tutorial explains how to use Excel Application.OnTime Method to trigger / schedule event at specified time. You may also want to read: Auto Open Excel with Windows Task Scheduler Excel automatically refresh pivot table Excel VBA Application.OnTime Method to trigger event at specified time You can do the followings with Excel VBA Application.OnTime Method - schedule a procedure to be run at a specified time in the future - schedule a procedure to be run after a specific amou...

Access Combo Box value depends on another Combo Box

Posted on April 4, 2015April 4, 2015 by Wyman W

This Access tutorial explains how to use Combo Box in Form where one Combo Box value depends on another Combo Box. You may also want to read: Access Form Combo Box and save selected value Access Combo Box value depends on another Combo Box In this tutorial, I will demonstrate how to use Combo Box in Access Form where one Combo Box value depends on another Combo Box using the below Login Form as an example. The Login Form contains two combo boxes - comboDivision: Contains a list of ...

Excel Fill Blank Rows or blank Cells in inactive Pivot Table

Posted on April 1, 2015March 24, 2016 by Wyman W

This Excel tutorial explains how to use the value above to fill blank rows (blank cells) in inactive Pivot Table in Tabular format. You may also want to read: Excel Delete Blank Rows Excel VBA reformat merged cells to row Fill Blank Rows or blank Cells Excel Pivot Table Fill Blank Rows In Pivot Table, when you group an item in column, the same grouping name does not repeat itself and leaving blank rows. In the below picture, Division contains blank rows. In Excel 2010 o...

Excel scatter chart using text name

Posted on March 31, 2015June 27, 2015 by Wyman W

This Excel tutorial explains how to ordinal data (text) in scatter chart using bar chart. Excel scatter chart using text name Excel scatter chart requires the x axis and y axis for each data point to be plotted in a diagram, and then join all those point together to analyze the trend or analyze if x and y variables are correlated, where X and Y values have to be numeric values. Below is an example showing how employee Grade is related to salary. From the graph, you can see the trend that Sa...

Access BETWEEN timestamp problem

Posted on March 31, 2015 by Wyman W

This Access tutorial explains the problem with BETWEEN timestamp, where the end date is not included in the result. Access BETWEEN timestamp problem - end date not included Very often you may receive data source that uses timeStamp (for example, 1/1/2015 15:34:00) instead of a Date (1/1/2015). The purpose of timestamp is to accurately record the time of action for auditing. In Access (even other system), you may want to select data within specific date period, say, between 1/1/2014 and 1/...

Excel VBA Add Table AutoFilter cancel AutoFilter sorting

Posted on March 30, 2015April 20, 2015 by Wyman W

This Excel tutorial explains how to use Excel VBA to add Table AutoFilter, cancel Table AutoFilter, sorting Table. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Worksheet AutoFilter Excel VBA Add Table AutoFilter or Cancel AutoFilter Using VBA to add AutoFilter or cancel Auto Filter is very simple if you know the code, but you should note th...

Excel VBA Add Autofilter Cancel AutoFilter sorting in worksheet

Posted on March 30, 2015July 11, 2015 by Wyman W

This Excel tutorial explains how to use Excel VBA to add AutoFilter, cancel AutoFilter, add sorting in Worksheet AutoFilter. You may also want to read: Sort data using custom Function by Bubble Sort Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Table AutoFilter Excel VBA Add AutoFilter or Cancel AutoFilter Using VBA to add AutoFilter or cancel Auto Filter is very simple if you know the code, but you should note that...

Excel use If condition on aggregate Function using Array

Posted on March 30, 2015March 30, 2015 by Wyman W

This Excel tutorial explains how to use If condition on aggregate Function using Array such as Average, Median, Mean, Maximum, Minimum. Excel use If condition on aggregate Function using Array Some Excel formula require you to input a range cells as argument in order to calculate a value, such as Sum, Count, Average, Median, Mean, Maximum, Minimum. However, in those formula, you cannot use If Condition on the data Range before calculating Sum, Count, Average, Median, Mean, Maximum, Minimu...

Access count substring in string or text

Posted on March 29, 2015September 15, 2015 by Wyman W

This Access tutorial explains how to count substring in string (text). Access count substring in string (text) Access does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a VBA solution for your reference. Access count substring in string using Len and Replace Function Access Len Function is to measure the length of text. Access Replace Function is to replace a substring in a text. To cou...

Excel count substring in string or text

Posted on March 29, 2015 by Wyman W

This Excel tutorial explains how to count substring in string (text). Excel count substring in string (text) Excel does not have a buit-in Function to count substring in string (text), instead you need to work around to get the job done. Alternatively, I have made a Macro solution for your reference. Excel count substring in string using Len and Substitute Function Excel Len Function is to measure the length of text. Excel Substitute Function is to replace a substring in a text. To c...