Adding Formula to cell in VBA

[rlpowers]

I have data being entered from a userform where the row is a variable. My goal would be to add the formula below to a column when data gets entered

=IF(ISBLANK(C2),"",DATE(YEAR(C2),MONTH(C2),DAY(C2)))

 ws.Cells(iRow, 4).Formula =

I can't seem to figure out how to get all the quotation marks correct to make the formula work. Any help would be appreciated.

[jeffreybrown]

Something like this

Sub Macro1()
    Dim irow As Long
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For irow = 2 To LR
        Cells(irow, 4).FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",DATE(YEAR(RC[-1]),MONTH(RC[-1]),DAY(RC[-1])))"
    Next irow
End Sub

I'm making a guess the formula in going in D2 and down to the last row of some column.

Without looping it could also be written as

Sub Macro2()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("D2:D" & LR).FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",DATE(YEAR(RC[-1]),MONTH(RC[-1]),DAY(RC[-1])))"
End Sub

[rlpowers]

Thanks that first option was the one I was looking for. It was the R1C1 that I was missing to make this work. Thanks again for the help!

[jeffreybrown]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[jeffreybrown]

Here is the easiest method to get it right in VBA (at least for me)

Put the formula in the first cell which in this case is D2.

Place you cursor on D2 >> turn on macro recorder >> select F2 >> select Enter

Now open the VBE and look at the new macro that was recorded.

Doing it this way ensures the right syntax is there everytime