# 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.

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 & "))"`

3. ## Re: Help with a VBA Formula

Or
Code:
` ActiveCell.FormulaR1C1 = "=(COUNTIF(C[23],"">""&AE))/(COUNT(C[23]))"`

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

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!

6. ## Re: Help with a VBA Formula

You're welcome & thanks for the feedback

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)`