Compute FormulaR1C1 on anther sheet (dynamic range)

dnaddeo

New Member
Joined
Dec 29, 2020
Messages
11
Office Version
  1. 2019
  2. 2016
  3. 2013
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
 

Attachments

  • Immagine 2020-12-31 151433.png
    Immagine 2020-12-31 151433.png
    61.7 KB · Views: 4
  • Immagine 2020-12-31 151454.png
    Immagine 2020-12-31 151454.png
    65.1 KB · Views: 5
  • Immagine 2020-12-31 151513.png
    Immagine 2020-12-31 151513.png
    30 KB · Views: 4

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
631
Office Version
  1. 365
Platform
  1. Windows
Hi. Try this:
VBA Code:
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
 
Solution

dnaddeo

New Member
Joined
Dec 29, 2020
Messages
11
Office Version
  1. 2019
  2. 2016
  3. 2013
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
 

Attachments

  • Immagine 2021-01-01 102010.png
    Immagine 2021-01-01 102010.png
    91.3 KB · Views: 2

dnaddeo

New Member
Joined
Dec 29, 2020
Messages
11
Office Version
  1. 2019
  2. 2016
  3. 2013
Hi. Try this:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,751
Messages
5,626,657
Members
416,199
Latest member
Gautamsunil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top