Vba relative reference developer tab in Excel
There are two ways by which a cell can be refered in Excel.
They are
1.Absolute reference
2.Relative reference
Absolute reference is one when the address of the cell remains constant irrespective of the logic.
For example, when cell A5 is refered from Cell A7, then it is actually "A5" and is always remain unchanged.
Whereas in in Relative reference, when cell A5 is refered from A7, then the excel is actually looking for 2 cells before A7.
This mean, when refered from A8, the excel will look for A6 and not A5.
This is called relative reference.
The macro recording, by default is absolute reference.
So, a macro recorded in a group of cells cannot be run on any other group of cells.
The following example illustrates this.
- Sub absolutemode()
- Range("J3").Select
- ActiveCell.FormulaR1C1 = "Naga"
- Range("J4").Select
- ActiveCell.FormulaR1C1 = "Sudhan"
- Range("J5").Select
- ActiveCell.FormulaR1C1 = "Saro"
- Range("J6").Select
- ActiveCell.FormulaR1C1 = "Vaibav"
- Range("J7").Select
- ActiveCell.FormulaR1C1 = "Sujeeth"
- Range("J8").Select
- ActiveCell.FormulaR1C1 = "Vaishu"
- Range("J9").Select
- ActiveCell.FormulaR1C1 = "Mom"
- Range("J10").Select
- ActiveCell.FormulaR1C1 = "da"
- Range("J10").Select
- ActiveCell.FormulaR1C1 = "Dad"
- Range("J11").Select
- End Sub
Executing the above macro will always start from cell J3.
Now consider the below macro with "Use relative reference" ON in the Developer tab.
- Sub relativerecord()
- ActiveCell.FormulaR1C1 = "Naga"
- ActiveCell.Offset(1, 0).Range("A1").Select
- ActiveCell.FormulaR1C1 = "Sudhan"
- ActiveCell.Offset(1, 0).Range("A1").Select
- ActiveCell.FormulaR1C1 = "Saro"
- ActiveCell.Offset(1, 0).Range("A1").Select
- ActiveCell.FormulaR1C1 = "Vaibav"
- ActiveCell.Offset(1, 0).Range("A1").Select
- ActiveCell.FormulaR1C1 = "Sujeeth"
- ActiveCell.Offset(1, 0).Range("A1").Select
- ActiveCell.FormulaR1C1 = "Vaishu"
- ActiveCell.Offset(1, 0).Range("A1").Select
- ActiveCell.FormulaR1C1 = "Mom"
- ActiveCell.Offset(1, 0).Range("A1").Select
- ActiveCell.FormulaR1C1 = "Dad"
- ActiveCell.Offset(1, 0).Range("A1").Select
- End Sub
The above macro will run from the currently selected cell.