Help with a VBA Formula
Results 1 to 7 of 7

Thread: Help with a VBA Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help with a VBA Formula

    Hello All,

    I've been working on a project and I'm stuck when it comes to converting an Excel Formula into a VBA script.

    I've decided to automate a bit of a program, so Yint is the beginning point, and Yfin is the ending point.

    The Excel Formula is
    Code:
     =(COUNTIF(AI10:AI18,">"&AE10))/(COUNT(AI10:AI18)) (
    I've tried to implement it using
    [CODE] Worksheets("SHEET1").Range("A1").Formula = "=countif(" & chr(34) & "AI" & chr(34)& yint & ":" & chr(34) & "AI" & chr(34) & yfin) chr(34) & ">" & chr(34) & "AE" & yint))/count("AI" & yint & ":" & "AI" & yfin))"

    But I seem to not be able to get this to work. Any tips?
    Thank you.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,731
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Help with a VBA Formula

    Hi & welcome to MrExcel.
    How about
    Code:
    Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    130
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a VBA Formula

    Or
    Code:
     ActiveCell.FormulaR1C1 = "=(COUNTIF(C[23],"">""&AE))/(COUNT(C[23]))"
    Last edited by mohadin; Aug 18th, 2019 at 11:23 AM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,731
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Help with a VBA Formula

    @mohadin
    You cannot mix R1C1 & A1 references like that.
    Also if the active cell is in col A it will be looking at col X not col AI
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with a VBA Formula

    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel.
    How about
    Code:
    Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"
    This worked Excellently, thank you very much!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,731
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Help with a VBA Formula

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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

    Default Re: Help with a VBA Formula

    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel.
    How about
    Code:
    Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"
    Personal preference I guess, but I always find it easier to read formulas (text) without multiple concatenations, so I like to use stand-in characters (unique to the text being processed) for variables (so I can see the structure of the formula better... see the blue highlight) and then substitute the actual values from the variable afterwards. Doing that for your line of code yields this...
    Code:
    Range("A1").Formula = Replace(Replace("=COUNTIF(AI@:AI#,"">""&AE@)/COUNT(AI@:AI#)", "@", yint), "#", yfin)
    Last edited by Rick Rothstein; Aug 18th, 2019 at 12:06 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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
  •