Moving around the Worksheet in VBA for Excel

Formulas and Values in VBA for Excel

Value

When you want to enter a numerical value in a cell you will write:
Range("A1").Value = 32

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:
Range("A1").Value = 32
You can even change the value of cells on another sheet with:
Sheets("SoAndSo").Range("A1").Value = 32

You can also enter the same value in many cells with:
Range("A1:B32").Value = 32

If you want to enter a text string in a cell you need to use the double quotes like:
Range("A1").Value = "Peter" will return Peter. Notice the quotation marks.

If you want to enter a text with double quotes showing in the cell you need to triple the double quotes like:
Range("A1").Value = """ Peter""" will return "Peter" with quotation marks

Formula

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:
Range("A1").Select
Selection.Formula = "=C8+C9"
Note the two equal signs (=) including the one within the double quotes like if you were entering it manually.

Again you don't need to select a cell to enter a formula in it. From anywhere on the sheet you can write:
Range("A1").Formula = "=C8+C9"

If you write the following:
Range("A1:A8").Formula = "=C8+C9"
The formula in A1 will be =C8+C9, the formula in A2 will be =C9+C10 and so on. If you want to have the exact formula =C8+C9 in all the cells, you need to write:
Range("A1:A8").Formula = "=$C$8+$C$9"

FormulaArray

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:
Range("A1:A8").FormulaArray = "=INDEX($D$11:$D$18,MATCH(1,($A$11:$A$18=H20)*($B$11:$B$18=I20)*($C$11:$C$18=J20),0),1)"

FormulaR1C1

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:
Range("C1,H1").FormulaR1C1 = "=RC[-2]+RC[-1]"

If you want a formula that says "sum the cell 2 rows above and the cell one row above".
Range("C1,H1").FormulaR1C1 = "=R[-2]C+R[-1]C"

Discover more tips
and practical examples of real solutions to real problems
with step by step development instructions in the 25

Resource Spreadsheets