Compute FormulaR1C1 on anther sheet (dynamic range)

[dnaddeo]

HI to all, here another question:
I have attached the Cotizaciones (sheet 1) , Retornos (sheet2) and Descriptiva (sheet3) . From data in Retornos (sheet 2), for each column (are dynamics) starting from B3, I have to compute calcolation of average and the volatility (until last row for each column, which are as well dynamic) for each column. As you can see, the oucomes have to be past in Descriptiva (sheet3) for each column available within the sheet Cotizaciones. I have highlighted, in green, the cells in which the outcomes must be entered
Here the code:
'compute average and volatility
Sub Descriptvios()
'copiar los nombres de los titulo
Dim lastcolumn As Long
Dim lastrow As Long

lastcolumn = Worksheets("Cotizaciones").Cells(1, Columns.Count).End(xlToLeft).Column
Worksheets("Cotizaciones").Range("B1", Cells(1, lastcolumn)).Copy
Sheets("Descriptiva").Select
Range("B1").Select
ActiveSheet.Paste

Cells(3, 1).Value = "Media diaria"
Cells(4, 1).Value = "Volatilidad diaria"
Cells(7, 1).Value = "Media anual"
Cells(8, 1).Value = "Volatilidad anual"

' Descriptvios calculos
lastrow = Worksheets("Cotizaciones").Cells(Rows.Count, 1).End(xlUp).Row

Range("B3").Select
'Range("B3:B", Cells(3, lastcolumn)).FormulaR1C1 = "=AVERAGE(Retornos!RC:R[250]C)"

Range("B4").Select
Range("B4", Cells(4, lastcolumn)).FormulaR1C1 = "=VAR.S(Retornos!RC:R[249]C)"

Range("B7").Select
Range("B7", Cells(7, lastcolumn)).FormulaR1C1 = "=R[-4]C*250"

Range("B8").Select
Range("B8", Cells(8, lastcolumn)).FormulaR1C1 = "=R[-4]C*250"

End Sub



If facing problem on do the part of the formula FormulaR1C1 for all the calcolations, as RC:R[250]C)", RC:R[249]C)", =R[-4]C*250", "=R[-4]C*250" have dynamic raws( as can be 250 or 500 ecc.
So the codes to check are from ' Descriptvios calculos

Thanks, Davide

 

[Osvaldo Palmeiro]

Hi. Try this:

Sub DescriptviosV2()
 Dim lastcolumn As Long, lastrow As Long
  Sheets("Descriptiva").Cells = ""
  With Sheets("Cotizaciones")
   lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
   lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
   .Range("B1", Cells(1, lastcolumn).Address).Copy Sheets("Descriptiva").Range("B1")
  End With
  With Sheets("Descriptiva")
   .Cells(3, 1).Resize(2) = Application.Transpose([{"Media diaria","Volatilidad diaria"}])
   .Cells(7, 1).Resize(2) = Application.Transpose([{"Media anual", "Volatilidad anual"}])
   .Range("B3", .Cells(3, lastcolumn).Address) = "=AVERAGE(Retornos!B3:B" & lastrow & ")"
   .Range("B4", .Cells(4, lastcolumn).Address) = "=VAR.S(Retornos!B3:B" & lastrow & ")"
   .Range("B7", .Cells(8, lastcolumn).Address) = "=B3*" & lastrow - 2
  End With
End Sub

 

[dnaddeo]

Dear Osvaldo Palmeiro,

good morning, happy new year and thanks for giving tume for me. The code works but i have just add two line as "Media anual" and "Volatilidad anual" are fixed . What i mean is that the moltiplication has to be run for 250 (or 252) days and are related to my old code. So i have wrote as follow ad is work:
copiar los nombres de los titulo, attached also the photo:

With Sheets("Descriptiva")
.Cells(3, 1).Resize(2) = Application.Transpose([{"Media diaria","Volatilidad diaria"}])
.Cells(7, 1).Resize(2) = Application.Transpose([{"Media anual", "Volatilidad anual"}])

'calcolation
.Range("B3", .Cells(3, lastcolumn).Address) = "=AVERAGE(Retornos!B3:B" & lastrow & ")"
.Range("B4", .Cells(4, lastcolumn).Address) = "=VAR.S(Retornos!B3:B" & lastrow & ")"
'.Range("B7", .Cells(8, lastcolumn).Address) = "=B3*" & lastrow - 2
Range("B7", Cells(7, lastcolumn)).FormulaR1C1 = "=R[-4]C*250"
Range("B8", Cells(8, lastcolumn)).FormulaR1C1 = "=R[-4]C*250"
End With

End Sub

Now i will wrote another mex as i have the same issue for other two lines, hope you can check it as well.

Thanks, Davide

 

[dnaddeo]

Hi. Try this:

Sub DescriptviosV2()
Dim lastcolumn As Long, lastrow As Long
  Sheets("Descriptiva").Cells = ""
  With Sheets("Cotizaciones")
   lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column
   lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
   .Range("B1", Cells(1, lastcolumn).Address).Copy Sheets("Descriptiva").Range("B1")
  End With
  With Sheets("Descriptiva")
   .Cells(3, 1).Resize(2) = Application.Transpose([{"Media diaria","Volatilidad diaria"}])
   .Cells(7, 1).Resize(2) = Application.Transpose([{"Media anual", "Volatilidad anual"}])
   .Range("B3", .Cells(3, lastcolumn).Address) = "=AVERAGE(Retornos!B3:B" & lastrow & ")"
   .Range("B4", .Cells(4, lastcolumn).Address) = "=VAR.S(Retornos!B3:B" & lastrow & ")"
   .Range("B7", .Cells(8, lastcolumn).Address) = "=B3*" & lastrow - 2
  End With
End Sub

Hi Osvaldo, I have create another post: VBa -useform dynamic - stock portolio weight