Vba add relative formula in cell in Excel
For example, I want to enter a formula "=IFERROR(C2/B2,"") in cell D2 as shown below.
To do it in Excel, here is the answer:
- Option Explicit
- Sub AddFormula()
- ActiveSheet.Range("D2").Formula = "=Iferror(R[0]C[-1]/R[0]C[-2],"""")"
- 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:
You can find similar Excel Questions and Answer hereunder
1) How do I restrict user entry in ActiveX Text Box to just numeric values?
2) How to do workbook protection with VBA in Excel
3) Vba code to password protect workbook in Excel
4) Highlight row of selected cell in Excel
5) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?
6) How do I know which cells on the worksheet contain Conditional Formatting?
7) How to display messages boxes in VBA with the msgbox function
8) How can I export a WorkSheet as a PDF using VBA?
9) Vlookup to return multiple values in same cell in Excel
10) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?