This Excel VBA tutorial explains how to get value before Worksheet_Change event.
Problem with getting value before Worksheet_Change event
When we use Worksheet_Change event, the event is triggered after we edit a Cell (even no value is changed). For example, when A1 value is 10, we double click range A1 to change value to 20. As we press enter, Worksheet_Change event is triggered. As you can see, the old value 10 is not stored anywhere, but sometimes we do need the old value to do calculation....
Read More
Excel
Excel Find records of Minimum Date or Maximum Date
This tutorial explains how to find minimum date and maximum date of each record in Excel by sorting and remove duplicate.
You can find more tutorials at http://Access-Excel.tips
Find records of Minimum Date or Maximum Date, Why?
Find records of minimum date or maximum date is an essential skills in employee records. Some have data use an unique key such as employee ID plus a date as the unique record. For example, when Peter (Employee ID 001) was hired on 1/1/2014 and then terminated on 1/5...
Read More
Excel Networkdays function to exclude weekend
This tutorial explains how to use Excel Networkdays Function to exclude weekend and holiday.
Excel Networkdays function to exclude weekend and holiday
Excel Networkdays Function literally means "net working days", which calculates the number of days between two specific days, excluding weekend (Saturday and Sunday) and holiday. Both start date and end date inclusive.
For example, from Jan 1st, 2015 (Thu) to Jan 5th, 2015 (Monday), total number of Networkdays is three. The function counts...
Read More
Excel VBA Round Function and custom Round Function
This tutorial explains difference between Excel VBA Round Function and Excel worksheet Round Function, and create a custom Excel Round Function.
Excel VBA Round Function and custom Round Function
Excel VBA Round Function is completely different from Excel worksheet Round Function.
For Excel spreadsheet Round Function, if decimal part is >=0.5, the integer rounds up, otherwise the integer rounds down.
For Excel VBA Round Function, as well as Access VBA, the Round Function uses "Round t...
Read More
Excel VBA FollowHyperlink Method
This Excel VBA tutorial explains how to use VBA FollowHyperlink Method to open website.
VBA FollowHyperlink Method
FollowHyperlink is used to tell Excel to open up a browser and go to a specific website, or download a file in specific folder, depending on your "Hyperlink" address.
In Excel spreadsheet, there is a similar worksheet function called Hyperlink, but it converts a text to hyperlink but FollowHyperlink function actually "go to" hyperlink.
Syntax of FollowHyperlink Method
expre...
Read More
Excel worksheet R1C1 reference style
This Excel tutorial explains how to use R1C1 reference style in Excel worksheet.
Excel worksheet R1C1 reference style
In R1C1, "R" refers to row, while "C" refers to column. Formula R1C1 allows you set formula for a cell, using relative row and column.
For example, you want to set a formula in C2 to sum the two cells on the left, before that you should ask yourself a question:
Do I want to sum the cell on left 1 and left 2 relative to C2, or
Do I just want to sum A2 and B2, no matte...
Read More
Excel VBA FormulaR1C1 Property of Range
What is FormulaR1C1 Property in Excel VBA?
In R1C1, "R" refers to row, while "C" refers to column. Formula R1C1 allows you set or retrieve formula for a cell, using relative row and column.
Why do you need FormulaR1C1?
For example, you want to set a formula in C2 to sum A2 and B2, you can type
Range("C2").formula= "=A2+B2" or
Range("C2").Value = "=A2+B2"
Instead of hard code "A2+B2", you can also tell Excel to sum the two cells on the left of the formula cell using FormulaR1C1.
"A2+B2...
Read More
Excel draw stacked chart with empty future data
Solution to draw Excel stacked chart with empty future data
First of all, it is true that Excel stacked chart would consider empty data as "0" instead of ignoring them like the case in non-stacked chart, it is the behavior that cannot be changed.
In order not to change data range every week, it is better to set dynamic data range so that if there is no data in December 19, the data range (both data and x-axis label) is resized to December 12.
You can find the solution in the below art...
Read More
Excel COUNTA Function to count non-empty cells
What does Excel COUNTA Function do?
Excel COUNTA Function is used to count non-empty cells in the specified range, regardless whether it is text, space, date or number.
Unlike COUNT Function, which only counts "number". Read the below article for explanation of COUNT function.
Excel worksheet COUNT function
Why do you need Excel COUNTA Function?
Normally, it is used to count non-empty cells, but it is more commonly used to determine the last row in the column. For example, assume that ...
Read More
Excel COUNT Function
What does Excel COUNT Function do?
Excel COUNT function is used to count how many "number" in the specified range. Note that "number" in text format (with the green triangle on the top left) is not counted.
It can be used to count non-empty cells in a column that contains number.
You can also read the below article that explains COUNTA Function, which counts non-empty values regardless of numeric or text.
Excel worksheet COUNTA Function
Syntax of Excel COUNT Function
=COUNT(value...
Read More