vba add relative formula in cell in Excel

Q166. In Excel, how do I enter a formula in a cell using VBA (using Relative Reference)?

For example, I want to enter a formula "=IFERROR(C2/B2,"") in cell D2 as shown below.

excel vba add relative formula in cell

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub AddFormula()
  3. ActiveSheet.Range("D2").Formula = "=Iferror(R[0]C[-1]/R[0]C[-2],"""")"
  4. End Sub

Description:

a) Line 3 adds the formula in Range D2.

b) C2 is one Column to the left of D2 in the same row. So, it is entered as R[0]C[-1].

c) B2 is two Columns to the left of D2 in the same row. So, it is entered as R[0]C[-2].

d) Notice the use of """" to introduce a quote within the formula string.

Result after Macro execution:

excel excel vba add relative formula in cell

 

You can find similar Excel Questions and Answer hereunder

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

2) How can I protect / unprotect WorkSheet using VBA?

3) How can I delete all shapes in a WorkSheet?

4) How can I add interval to dates?

5) How can I set non-contiguous print area using VBA?

6) Applying a countif formula only to visible cells in a filtered list in Excel

7) How can I dynamically add series to an existing chart using VBA?

8) How can I calculate values applying complex Engineering formulas using Excel?

9) Vlookup to return multiple values in same cell in Excel

10) Vba code to password protect workbook in Excel

11) How can I loop through all WorkSheets and get their names using VBA?

12) How can I activate a routine when there is a change in value of a cell?

13) Excel 2010 vba replacement of application filesearch in Excel

14) How can I list all files in a folder using VBA?

15) Vba delete entire row if contains certain text in Excel