# Compute FormulaR1C1 on anther sheet (dynamic range)

##### New Member
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(7, 1).Value = "Media 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
61.7 KB · Views: 4
• Immagine 2020-12-31 151454.png
65.1 KB · Views: 5
• Immagine 2020-12-31 151513.png
30 KB · Views: 4

### Excel Facts

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
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
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``````

##### New Member
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
91.3 KB · Views: 2

##### New Member
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
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

Replies
10
Views
589
Replies
0
Views
102
Replies
15
Views
504
Replies
1
Views
65
Replies
20
Views
738

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.

### Which adblocker are you using?

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

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