Updating active cells in vba
The Range property takes the same argument that most Excel Worksheet functions take e.g. The following example shows you how to place a value in a cell using the Range property.
As you can see Range is a member of the worksheet which in turn is a member of the Workbook.
This follows the same hierarchy as in Excel so should be easy to understand.
To do something with Range you must first specify the workbook and worksheet it belongs to.
Using In the above example, we are using a number for the column rather than a letter.
To use Range here would require us to convert these values to the letter/number cell reference e.g. Using the Cells property allows us to provide a row and a column number to access a cell.
The reason this is useful is that sometimes you may want to select a Range based on a certain condition.
For example in the screenshot below there is a column for each day of the week. Monday=1, Tuesday=2 etc.) we need to write the value to the correct column.
Let’s start with the simplest method of accessing cells – using the Range property of the worksheet.
For the rest of this post I will use the code name to reference the worksheet.
The following code shows the above example using the code name of the worksheet i.e.
I have recently updated this article so that is uses Value2.
You may be wondering what is the difference between Value, Value2 and the default: as it will always return the actual cell value(see this Stack Overflow answer.) The worksheet has a Range property which you can use to access cells in VBA.This can come in very handy when you are debugging or writing code for the first time.