Help with a VBA Formula

IDislikeVBA

New Member
Joined
Aug 18, 2019
Messages
4
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.
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Or
Code:
 ActiveCell.FormulaR1C1 = "=(COUNTIF(C[23],"">""&AE))/(COUNT(C[23]))"
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
@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

New Member
Joined
Aug 18, 2019
Messages
4
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,039
Office Version
2010
Platform
Windows
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("[B][COLOR="#0000FF"]=COUNTIF(AI@:AI#,"">""&AE@)/COUNT(AI@:AI#)[/COLOR][/B]", "@", yint), "#", yfin)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,367
Messages
5,486,443
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top