Results 1 to 5 of 5

Thread: Puzzled by VBA formula

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    551
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Puzzled by VBA formula

    Code:
    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?

  2. #2
    Board Regular
    Join Date
    Nov 2016
    Posts
    605
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Puzzled by VBA formula

    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

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Puzzled by VBA formula

    Try this.
    Code:
    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
    If posting code please use code tags.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Puzzled by VBA formula

    Quote Originally Posted by Big Lar View Post
    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))”
    Last edited by Rick Rothstein; Jan 16th, 2019 at 01:54 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    May 2002
    Posts
    551
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Puzzled by VBA formula

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

    Thanks for the explanation.
    It does indeed work.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •