Filling formula for a wide range of cells.

[VKu]

Hi,

I need to put in functions/formula in a range of cells from C4:AEM121
With my reference cell being A4:A121 and another reference cell being C2:AEM2

for the first cell C4 the formula would be
=TR(C2,"TR.CompanyMarketCap","SDate=#1",,A4)

for the second cell C5 the formula would be
=TR(C2,"TR.CompanyMarketCap","SDate=#1",,A5)

for the first cell D4 the formula would be
=TR(D2,"TR.CompanyMarketCap","SDate=#1",,A4)

and so on. Please see attached file

I could fix the reference cell C2 by using $C$2, but I would have to do this for all the columns until the last column AME which is quite tedious.
Thus I'd like to know if there are any way to fill in the functions more easily and less time consuming, or perhaps a VBA macro could be used?


Thank you for suggestions and help.

Vku

[mehmetcik]

This is so easy.

Put all your formulas into one row of your spreadsheet.

Start the macro recorder and select each cell and the one below it in turn in turn, click on the formula and press enter.
stop the macro editor.

you will have something like:-

Please Login or Register  to view this content.

ou can edit that down to:-

Please Login or Register  to view this content.

Once you have edited all your code you need two more things.

First to count the number of rows.

Please Login or Register  to view this content.

finds the last used row in column 1

Please Login or Register  to view this content.

finds the last used row in column 2

Finally use find / replace to replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Replacing all occurrences.

[VKu]

Thank you mehmetcik,

the code

Sub Macro1()
Range("C4").FormulaR1C1 = "=TR(R2C3,""TR.CompanyMarketCap"",""SDate=#1"",,RC[-2])"
End Sub

works well for the cell C4, but i'm not quite sure how to implement it to cover the whole range of cells.

could you please elaborate, i'm quite new to VBA

Many thanks!

Vku

[mehmetcik]

I get errors here.

so I cannot do it for you.

Please Login or Register  to view this content.

Will put that formula in 4 cells C4, C5, D4, D5


I have created this Macro to help you.


If you copy your recorded macro into column A of an excel spreadsheet and run this macro,

It will tidy up your macro for you.

Once its done its job, copy the macro back to visual basic.

Please Login or Register  to view this content.