# Help with a VBA Formula

#### IDislikeVBA

##### New Member
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.``````

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Fluff

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

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

Last edited:

#### Fluff

##### MrExcel MVP, Moderator
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

#### IDislikeVBA

##### New Member
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!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### Rick Rothstein

##### MrExcel MVP
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("[B][COLOR="#0000FF"]=COUNTIF(AI@:AI#,"">""&AE@)/COUNT(AI@:AI#)[/COLOR][/B]", "@", yint), "#", yfin)``

Last edited: