Puzzled by VBA formula

[Big Lar]

Dim Column_L_L As Long
Set ws = Sheets("Members")
    Column_L_L = ws.Range("A" & Rows.Count).End(xlUp).Row
       ws.Range("L2:L" & ws.Range("A" & Rows.Count).End(xlUp).Row).Formula = _
"=YEARFRAC(K2, TODAY(), 1)"
 
        ws.Calculate

This bit of code creates an issue in my project.
If cells in ColumnK are blank, adjoining cell L values = 119. This producing undesired results later in the project.

In an attempt to resolve this issue, I’ve revised the formula to:
“=IF(K2="”,"”,(YEARFRAC(K2,TODAY(),1)))”

In Runtime, the formula populates as:
“=IF(K2=",",(YEARFRAC(K2,TODAY(),1)))”

This is puzzling. What am I doing wrong?

 

[JumboCactuar]

What I would do here to get the right formula,

Record Macro
Click on cell with formula and enter
Stop macro and view code

Then copy the formula part into your macro

 

[Norie]

Try this.

Dim Column_L_L As Long
 
    Set ws = Sheets("Members")

    Column_L_L = ws.Range("A" & Rows.Count).End(xlUp).Row

    ws.Range("L2:L" & ws.Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(K2="""","""",YEARFRAC(K2,TODAY(),1))"
 
    ws.Calculate

 

[Rick Rothstein]

In an attempt to resolve this issue, I’ve revised the formula to:
“=IF(K2="”,"”,(YEARFRAC(K2,TODAY(),1)))”

In Runtime, the formula populates as:
“=IF(K2=",",(YEARFRAC(K2,TODAY(),1)))”

This is puzzling. What am I doing wrong?

Quote marks inside of the quote marks that define a text constant must be doubled up (otherwise VB cannot tell if you are trying to end the text constant early). Try it this way and it should work...

“=IF(K2="”"","""”,YEARFRAC(K2,TODAY(),1))”

 

[Big Lar]

Norie and Rick!!!
To the rescue, again!

Thanks for the explanation.
It does indeed work.