Excel VBA FollowHyperlink Method

Posted on December 25, 2014March 2, 2016 by Wyman W

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...

Excel worksheet R1C1 reference style

Posted on December 25, 2014January 30, 2017 by Wyman W

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...

Excel VBA FormulaR1C1 Property of Range

Posted on December 24, 2014January 8, 2015 by Wyman W

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...

Excel draw stacked chart with empty future data

Posted on December 24, 2014March 31, 2015 by Wyman W

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...

Excel COUNTA Function to count non-empty cells

Posted on December 24, 2014October 16, 2015 by Wyman W

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 ...

Excel COUNT Function

Posted on December 24, 2014January 7, 2015 by Wyman W

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...

Excel graph dynamic data range

Posted on December 24, 2014January 3, 2016 by Wyman W

This Excel tutorial explains how to set Excel graph dynamic data range. You may also want to read: Excel dynamic Data Validation list Excel Dynamic Print Area Excel dynamic data range Excel graph dynamic data range The purpose of Excel graph dynamic data range is to select data range based on how many data you have input instead of using a fixed range. Assume that you have the data set below. You can draw a graph from January to October easily by selecting A1 to B11, bu...

Excel OFFSET Function for worksheet

Posted on December 23, 2014January 9, 2015 by Wyman W

What is Excel OFFSET Function? Excel OFFSET Function returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell. For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to the right. (shift 1 column to the right and shift 0 row). The advantage of using OFFSET property is that even when the actual column change, you don't ...

Excel VBA OFFSET Property for Range

Posted on December 23, 2014February 26, 2017 by Wyman W

This Excel VBA tutorial explains how to use Excel VBA OFFSET property, and explain difference between VBA OFFSET Property and worksheet OFFSET Function. Excel VBA OFFSET Property Excel VBA OFFSET property returns the Range object which is number of rows and columns away from a specific range. You can specify to shift up, down, left or right from a cell. For example, if column A contains employee ID, and column B contains employee name, you can find the employee name in B2 by shifting A2 to ...

Excel VBA run hyperlink

Posted on December 18, 2014February 25, 2015 by Wyman W

Solution to trigger hyperlink Use FollowHyperlink function to go to a website. Public Sub hyper() ThisWorkbook.FollowHyperlink Range("C6").Value End Sub For details of the FollowHyperlink, check out the below article Excel VBA function FollowHyperlink