VBA for Excel Macros

Handy Hint: If you want a short cut key that does not exist, make your own by attaching a macro to a short cut key so it does your required function

Here are a few of my favourite VBA sentences for macros

Range("A1").Select

Do Until ...

Loop

A loop is a great way to repeat a task until a set condition has been met.

Eg.

Do Until ActiveCell = "Stop"

ActiveCell.Offset(1,0).Select

Loop 

This will move the cell selected down until the cell has the text Stop.

If ... Then

....

Else

....

End If

An IF statement can check for a certain condition, and do different actions depending on whether this condition has been met or not.

Eg.

If ActiveCell.Offset(0,-3)>0 Then

Selection.FormulaArray ="=SUM(IF(data!R2C5:R16C5=RC[-3],data!R2C4:R16C4,0))"

Else

ActiveCell.Offset(1,0).Select

End If

This will see if the cell 3 to the left is greater than 0. If it is it enters the formula, otherwise it moves down one row.

ActiveCell.Offset(1,0).Select

Offset moves the cell selected to another cell. The first number is how many rows you want to move down. The second number is how many columns you wish to move to the right.

If you wish to move up or to the left, put in a negative number.

ActiveCell.FormulaR1C1 = "Text"

This puts the word Text into the cell you are in.

ActiveCell.FormulaR1C1= "=VLOOKUP(RC[-1],Data,3,FALSE)"

This will write a vlookup formula into your active cell. On this example it looks up the value in the cell to the left of the active cell, into the area called Data, and returns a value from column 3 for an exact match.

Instead of refering to cells in the usual format eg A3, above it uses R(Row)C(Column). The -1 after C means use one column to the left.

We usually use $ to absolute a cell reference. Here we use [ ] to show non absolute references, and no brackets if the reference is absolute.

rowcount = ActiveSheet.UsedRange.Rows.Count

For i =1 To rowcount -1

......

Next i

This counts the number of used rows in the sheet and for that number of times less one it will perform a task.

Selection.EntireRow.delete

Selection.ClearContents

You can then add them together to do something like this:

Range("A1").Select

rowcount = ActiveSheet.UsedRange.Rows.Count

For i = 1 To rowcount

If ActiveCell = "Text" Then

Selection.ClearContents

ActiveCell.Offset(1,0).Select

Else

ActiveCell.Offset(1,0).Select

End If

Next i

The above tells the macro to start in cell A1, then for the length of the spreadsheet that is used if the cell says "Text" clear this and move down a cell, otherwise move down to the next cell.