VBA Help for Formulas

[zaska]

Hello,

I am using the following two formulas and i kindly request anyone to help me to write a macro for the same.

=PERCENTRANK(B$2:B$500,B2)

=AND(D2>=PERCENTILE(D$2:D$500,0.17),D2<=PERCENTILE(D$2:D$500,0.42))

Thank you

 

[bensonsearch]

Hello,

I am using the following two formulas and i kindly request anyone to help me to write a macro for the same.

=PERCENTRANK(B$2:B$500,B2)

=AND(D2>=PERCENTILE(D$2:D$500,0.17),D2<=PERCENTILE(D$2:D$500,0.42))

Thank you

you can call most worksheet functions in VBA, an example of vlookup is below


=VLOOKUP(123,A1:C100,3,FALSE)

To use code in VBA that does the same thing, you would use:


Dim Res As VariantRes = Application.WorksheetFunction.VLookup(123,Range("A1:C100"),3,FALSE)</PRE>

 

[Boller]

Do you want the macro to enter the formulas or do you it to enter the results?

In what cell or cells do want the formulas (or results) entered?

 

[SydneyGeek]

To record a macro that creates the formulas, do this:

Start the macro recorder
Select the first cell with the formula that you want to record
Press {F2} then {TAB}
Select the next formula cell and repeat.

That will write a macro creating the formulas.
To fill those down is straightforward but I need to know the layout of your worksheet.
To convert them to values afterwards you can copy the formulas, then paste special > values.

Denis