• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert from .FormulaR1C1 to .Application.WorksheetFunction

benedict98

New Member
Hello Excel enthusiasts,

I would really appreciate some help on this one...

I am trying to convert:
.FormulaR1C1 = "=COUNTIFS('Sheet1'!C7,""X"",'Sheet1'!C2,RC1,'Sheet1'!C12,""*""&R1C&""*"")/R2C"

into a .Application.WorksheetFunction format to speed up my macro.

[RC1,'Sheet1'!C12,""*""&R1C&""*"")/R2C"] these all need to be dynamic, but the first condition doesn't.


Please help!
 

BobBridges

Active Member
Just to be sure you understand, you can put an R1C1 formula into many cells, but if you use an Application.WorksheetFunction instead it'll just be for one result at a time. Maybe that's what you want, but I'm uncertain because you also refer to cells RC1 and R1C; you'd have to have particular cells in mind to make this work. Am I missing something?
 

p45cal

Well-Known Member
Best attach a workbook and tell us which cells you want the formula in; there are just to many unknowns to be able to advise sensibly without one.
 

benedict98

New Member
Hello, just replacing the formula with its result just adding this codeline : .Formula = .Value …​
Thanks Marc!

So I can just put .Value at the end of my .FormulaR1C1 and it will only return the value?


I really appreciate your help on this one.
 
Top