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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi & welcome to MrExcel.
How about
Code:
Range("A1").Formula = "=(COUNTIF(AI" & yint & ":AI" & yfin & ","">""&AE" & yint & "))/(COUNT(AI" & yint & ":AI" & yfin & "))"
 
Upvote 0
Or
Code:
 ActiveCell.FormulaR1C1 = "=(COUNTIF(C[23],"">""&AE))/(COUNT(C[23]))"
 
Last edited:
Upvote 0
@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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top