Vba excel Formula Computation?

Question

---

Hi all,i am new to vba please help me. I have a table like shown. The Days column is a formula column. the formula is simple it is the difference of Actual Date and Date. But the Actual Dateis present in another sheet (Sheet2) at Range("D5"). How to assign this formula programatically. I tried with Worksheets("Sheet1").Range("E2:E10").FormulaR1C1 = "=" & Worksheets("Sheet2").Range("D5").Value & "-Sheet1!RC[-1]" and Worksheets("Sheet1").Range("I6:I10").FormulaR1C1 = "=Sheet2!R[5]C[4]Sheet1!RC[-1]" unfornately both are giving wrong values. Thanks.

Answer 1

You can use the DATEDIF function to calculate the difference between two dates:

=DATEDIF(Sheet2!D5,Sheet1!E2,"D")

You will have to copy and modify this formula for the column, as this should work for the first cell.

Update:

Using VBA, you can use the below, which should work with your above dataset:

Worksheets("Sheet1").Range("E2:E10").FormulaR1C1 = "=DATEDIF(""" & Worksheets("Sheet2").Range("D5") & """,Sheet1!RC[-1],""D"")"

Answer 2

If the formula is from another sheet you can still reference the data on the current sheet.

Using R1C1 with formula:

Range("E2").FormulaR1C1 = "=Today() - R[0]C[-1]"

Adding just the number to the spreadsheet:

Range("E2").Value = DateDiff("d", Range("D2"), Now())

UPDATE:

For more than one value:

Worksheets("Sheet1").Range("E2:E10").FormulaR1C1 = "=Today() - R[0]C[-1]"