Autofill dynamic range formula - Stock price ln return

[dnaddeo]

Hi to all, I'm Davide and new on VBA code. I have an issue to help some one can help me. I Have already set a code to check the range dynamic of the data of the sheet named "Cotizaciones". Here the code:

Sub Funziona()
'Best used when first column has value on last row and first row has a value in the last column

Dim sht As Worksheet
Dim lastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Cotizaciones")
Set StartCell = Range("B1")

'Find Last Row and Column
lastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

'Select Range
sht.Range(StartCell, sht.Cells(lastRow, LastColumn)).Select

End Sub


Now i would add automatically a new sheet named "Retornos" where compute the log normal return calcolation froom the price available within sheet "Cotizaciones" but with dynamic fill. Here the partial code:

Sub calcular_retorno()

Dim i, j As Integer

'activa sheet Retornos

Worksheets("Retornos").Activate
Cells(3, 2).Select
' Calculo_retornos
'ActiveCell.FormulaR1C1 = "=LN(Cotizaciones!RC/Cotizaciones!R[-1]C)"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:K3"), Type:=xlFillDefault
Range("B3:K3").Select
Selection.AutoFill Destination:=Range("B3:K505")

End sub

The problem are these parts, as i would the autofill dynamic of the formulaLN(Cotizaciones!RC/Cotizaciones!R[-1]C)" as i would call the range captured from set code Sub Funziona(). Within the sheet Retornos the start cell is B3 but rows and colums as you know can change, depending from the data uploaded from sheet "Cotizaciones"
' Calculo_retornos
'ActiveCell.FormulaR1C1 = "=LN(Cotizaciones!RC/Cotizaciones!R[-1]C)"
Range("B3").Select
Selection.AutoFill Destination:=Range("B3:K3"), Type:=xlFillDefault
Range("B3:K3").Select
Selection.AutoFill Destination:=Range("B3:K505")

many thanks to all

Davide

 

[Fluff]

Glad we could help & thanks for the feedback.