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

1. ## 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.  Reply With Quote

2. ## Re: Help with a VBA Formula

Hi & welcome to MrExcel.
Code:
`Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"`  Reply With Quote

3. ## Re: Help with a VBA Formula

Or
Code:
` ActiveCell.FormulaR1C1 = "=(COUNTIF(C,"">""&AE))/(COUNT(C))"`  Reply With Quote

4. ## Re: Help with a VBA Formula

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  Reply With Quote

5. ## Re: Help with a VBA Formula Originally Posted by Fluff Hi & welcome to MrExcel.
Code:
`Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"`
This worked Excellently, thank you very much!  Reply With Quote

6. ## Re: Help with a VBA Formula

You're welcome & thanks for the feedback  Reply With Quote

7. ## Re: Help with a VBA Formula Originally Posted by Fluff Hi & welcome to MrExcel.
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)`  Reply With Quote

## User Tag List

#### Tags for this Thread

chr34, excel, formula, vba, yint #### Posting Permissions

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