Help with a VBA Formula

[IDislikeVBA]

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

 =(COUNTIF(AI10:AI18,">"&AE10))/(COUNT(AI10:AI18)) (

I've tried to implement it using

 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.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"

 

[mohadin]

Or

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

 

[Fluff]

@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

 

[IDislikeVBA]

Hi & welcome to MrExcel.
How about

Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"

This worked Excellently, thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Rick Rothstein]

Hi & welcome to MrExcel.
How about

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

Range("A1").Formula = Replace(Replace("[B][COLOR="#0000FF"]=COUNTIF(AI@:AI#,"">""&AE@)/COUNT(AI@:AI#)[/COLOR][/B]", "@", yint), "#", yfin)