How to loop a DATEDIF formula through a loop using VBA?

Question

I'm trying to loop a DATEDIF formula, using VBA, but I keep getting Run-time error '1004': Application-defined or object-defined error. I'm not sure why the code keeps kicking out. The formula itself works.

Sub DateandDaysFormula()


    With ThisWorkbook.Sheets("HDT Pivot Table")
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

        .Range(.Range("L2"), .Range("L" & lastRow)).Formula = _
            "=DATEDIF(RC[-5],TODAY(),'d')"
        .Range(.Range("X2"), .Range("X" & lastRow)).Formula = _
            "=IFERROR(VLOOKUP(D2,DataDrop!A:C,2,FALSE),0)"
    End With

End Sub

Where is your loop?

cybernetic.nomad
– cybernetic.nomad

2019-02-11 20:23:20 +00:00
Commented Feb 11, 2019 at 20:23 — cybernetic.nomad
– cybernetic.nomad, Commented Feb 11, 2019 at 20:23

teylyn · Accepted Answer · 2019-02-11 20:29:54Z

0

If you use RC notation in a formula, you need to use FormulaR1C1, not just Formula.

Also, the d must be in double quotes, not single quotes. If you need to enter double quotes inside a VBA string, you need to double them. So, the problematic line should work with this change:

.Range(.Range("L2"), .Range("L" & lastRow)).FormulaR1C1 = _
            "=DATEDIF(RC[-5],TODAY(),""d"")"

It works in my tests.

answered Feb 11, 2019 at 20:29

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to loop a DATEDIF formula through a loop using VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related