Moving around the Worksheet in VBA for Excel
Formulas and Values in VBA for Excel
When you want to enter a numerical value in a cell you will write:
Note that you don't need to select a cell to enter a value in it you can do it from anywhere on the sheet. For example from cell G45 you can write:
You can also enter the same value in many cells with:
If you want to enter a text string in a cell you need to use the double quotes like:
If you want to enter a text with double quotes showing in the cell you need to triple the double quotes like:
The best and easiest way to enter a formula programmatically in a cell (even if you are a pro) is to write it in a cell, select it with its equal sign from the formula bar and paste it between quotation marks in your code. This approach becomes even more friendly what you start developing complex formulas or start using variables and other cell's addresses within your formulas as shown in Part 5 of this lesson.
When you want to enter a formula in a cell you will write:
Again you don't need to select a cell to enter a formula in it. From anywhere on the sheet you can write:
If you write the following:
As you have discovered in the website on Excel you will not use array formulas a lot except when you need to use INDEX/MATCH with more than one argument. Such a formula would look like this:
Here is another formulation that you will only use in certain tight spots. Let's say that in cell C1 you want the formula "=A1+B1" and that in cell H1 you want the formula "=F1+G1". You can use a single formulation that will adapt to any cell that is selected. What you want is a formula that says "sum the cell 2 columns to the left and the cell one column to the left". Such a formula is:
If you want a formula that says "sum the cell 2 rows above and the cell one row above".
Discover more tips