Excel Made Easy

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.

  1. Sub absolutemode()
  2. Range("J3").Select
  3. ActiveCell.FormulaR1C1 = "Naga"
  4. Range("J4").Select
  5. ActiveCell.FormulaR1C1 = "Sudhan"
  6. Range("J5").Select
  7. ActiveCell.FormulaR1C1 = "Saro"
  8. Range("J6").Select
  9. ActiveCell.FormulaR1C1 = "Vaibav"
  10. Range("J7").Select
  11. ActiveCell.FormulaR1C1 = "Sujeeth"
  12. Range("J8").Select
  13. ActiveCell.FormulaR1C1 = "Vaishu"
  14. Range("J9").Select
  15. ActiveCell.FormulaR1C1 = "Mom"
  16. Range("J10").Select
  17. ActiveCell.FormulaR1C1 = "da"
  18. Range("J10").Select
  19. ActiveCell.FormulaR1C1 = "Dad"
  20. Range("J11").Select
  21. 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.

  1. Sub relativerecord()
  2. ActiveCell.FormulaR1C1 = "Naga"
  3. ActiveCell.Offset(1, 0).Range("A1").Select
  4. ActiveCell.FormulaR1C1 = "Sudhan"
  5. ActiveCell.Offset(1, 0).Range("A1").Select
  6. ActiveCell.FormulaR1C1 = "Saro"
  7. ActiveCell.Offset(1, 0).Range("A1").Select
  8. ActiveCell.FormulaR1C1 = "Vaibav"
  9. ActiveCell.Offset(1, 0).Range("A1").Select
  10. ActiveCell.FormulaR1C1 = "Sujeeth"
  11. ActiveCell.Offset(1, 0).Range("A1").Select
  12. ActiveCell.FormulaR1C1 = "Vaishu"
  13. ActiveCell.Offset(1, 0).Range("A1").Select
  14. ActiveCell.FormulaR1C1 = "Mom"
  15. ActiveCell.Offset(1, 0).Range("A1").Select
  16. ActiveCell.FormulaR1C1 = "Dad"
  17. ActiveCell.Offset(1, 0).Range("A1").Select
  18. End Sub

The above macro will run from the currently selected cell.

 

You can find similar Excel Questions and Answer hereunder

1) What is the design mode in the developer Tab in Excel

2) What is the xml group in the developer tab in Excel

3) What are the com addins in the developer tab of Excel

4) To easily identify sheets belonging to different Business Divisions, I would like to color code tab names - is it possible?

5) How to add email and emailing function in Excel VBA

6) How can I identify cells with Circular Reference in a WorkSheet?

7) What is the view code button in the Excel Developer Tab

8) How do I enter a formula in a cell using VBA (using Relative Reference)?

9) I want to pull data from a table using a reference cell in table. Is there a function to support that?

10) How do I enter a formula in a cell using VBA (using Absolute Reference)?

 

Here the previous and next chapter

Excel Made Easy was founded in 2002. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market.

We endeavor to provide the best service and support and to respond quickly to your questions.

Excel Made Easy is a proud sponsor of Dismantle-It.com the site that shows you the belly of your daily objects, Poupounette.com the site of cars loving cat and PowerPointMadeEasy.com the world top reference for PowerPoint Presentations questions and Pet Cemetery Online a site dedicated to your loving Pets.