VBA: Looping Formula

[YuthKu]

Hi, I have the following code

Sub formula()


Dim LastRow As Long
With ActiveSheet
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


Dim LastCol As Integer
With ActiveSheet
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With


Range("B2:AEL2").formula = "=TR(R[-1]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,R2C1)"
Range("B3:AEL3").formula = "=TR(R[-2]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,R3C1)"
Range("B4:AEL4").formula = "=TR(R[-3]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,R4C1)"
Range("B5:AEL5").formula = "=TR(R[-4]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,R5C1)"
Range("B6:AEL6").formula = "=TR(R[-5]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,R6C1)"

End Sub


I would like to repeat the formula from
Range("B2:AEL2").formula = "=TR(R[-1]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,R2C1)"

until the last row
Range("B LastRow: AEL LastRow").formula = "=TR(R[-LastRow+1]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,LastRowC1)"


Can anyone please help me write a loop for the above formula.
my reference cells are are ranges from from A2:A119 also B1:AEL1

 

[mole999]

you already have last row identified so

Dim n
for N = 2 to LR

Range("B" & LR & ":AEL" & LR).formula = "=TR(R[" & LR-1 & "]C[0],""TR.CompanyMarketCap"",""SDate=#1"",,R" & LR & "C1)"

next n

you can see the row number is replaced by LR and " & & " surround the insertion

 

[YuthKu]

Thank you mole999 for you feedback, however the syntax given does not work.
it just give out the result for the last row and not all the other rows.

 

[Rick Rothstein]

Give this a try...

Sub formula()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:AEL2").Resize(LR - 1).FormulaR1C1 = "=TR(R1C,""TR.CompanyMarketCap"",""SDate=#1"",,RC1)"
End Sub

The only difference between this and the output from your code is the relative instead of absolute cell references (which should not matter given that you will not be manually copying the formula down or across.

 

[YuthKu]

Thank you Rick Rothstein, the code you provided works well.
Much appreciate your time for replying to my post.

If not too much of an effort to you, could you please help me on implementing the VBA macro for me as below details,

I have data in cells ranging from B2:AEL[LR], with some of the cells containing values of #N/A
For cells with values #N/A, I would like to insert the formula =TR(XX,"TR.CompanyMarketCap","SDate=#1",,YY)

if cell R10 has the value #N/A, the formula for the cell would be
=TR(R1,"TR.CompanyMarketCap","SDate=#1",,A10)

if cell AC87 has the value #N/A, the formula for the cell would be
=TR(AC1,"TR.CompanyMarketCap","SDate=#1",,AC87)

that is
XX is always the 1st Row with the respective Column
YY is always the Column A and respective Row number

Thank you in Advance

VKu

 

[Rick Rothstein]

If not too much of an effort to you, could you please help me on implementing the VBA macro for me as below details,

I have data in cells ranging from B2:AEL[LR], with some of the cells containing values of #N/A
For cells with values #N/A, I would like to insert the formula =TR(XX,"TR.CompanyMarketCap","SDate=#1",,YY)

if cell R10 has the value #N/A, the formula for the cell would be
=TR(R1,"TR.CompanyMarketCap","SDate=#1",,A10)

if cell AC87 has the value #N/A, the formula for the cell would be
=TR(AC1,"TR.CompanyMarketCap","SDate=#1",,AC87)

that is
XX is always the 1st Row with the respective Column
YY is always the Column A and respective Row number

A couple of questions for clarification...

1) I have highlighted the text TR is your formula... what is it (you seem to use it as both a function and as an object)?

2) The address that I highlighted in red... should that not be A87 instead of AC87?

 

[YuthKu]

A couple of questions for clarification...

1) I have highlighted the text TR is your formula... what is it (you seem to use it as both a function and as an object)?

2) The address that I highlighted in red... should that not be A87 instead of AC87?

Hi Rick

To clarify,

1) The TR is the function that retrieves/pulls data from the Thomson Reuters Eikon via the excel add on,

2) Yes you are correct, the red highlight AC87 should be A87, referencing column A, row 87, My Bad!

Thank you

Virayuth.Ku

 

[Rick Rothstein]

Hi Rick

To clarify,

1) The TR is the function that retrieves/pulls data from the Thomson Reuters Eikon via the excel add on,

2) Yes you are correct, the red highlight AC87 should be A87, referencing column A, row 87, My Bad!

In looking at your original request and the code I gave you, it seems to me that you already have the formula you seek in those cells... are you saying my previous code did not do that for you? If so, select one of the cells with #N/A in it and post its formula in a reply here along with the address of the cell itself so I can see what formula my code actually generated.

 

[YuthKu]

In looking at your original request and the code I gave you, it seems to me that you already have the formula you seek in those cells... are you saying my previous code did not do that for you? If so, select one of the cells with #N/A in it and post its formula in a reply here along with the address of the cell itself so I can see what formula my code actually generated.

Hi Rick,

Your earlier code worked well, it applied the formula to each cell in the entire range of cell regardless of the cell having the value #N/A or not.

I would like to apply the formula
=TR(XX,"TR.CompanyMarketCap","SDate=#1",,YY)

only to the cells in the range that has the values #N/A. That is, let the macro find the cells in the range B2:AEL[LR] that contains the values #N/A, and then apply the formula to that individual cell.
with XX being the reference cell, that respective Column and Row 1
with YY being the reference cell, Column A and that respective row.

Thank you

Virayuth.Ku