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

 

[Saurabhj]

Hello Davide,

Kindly share the spreadsheet having data.

Thanks,
Saurabh

 

[dnaddeo]

Hello Davide,

Kindly share the spreadsheet having data.

Thanks,
Saurabh

Dear Saurabh,

good morning and thanks for helping me. I have shared the excel file named "Trial". Is the one which i'm using for analyze and trying different codes. Within the module "Calculation" I have the code Sub Funziona() in order to select the rows and columns which are dynamic (rows and colums can vary) from sheet Cotizaciones. The Sub calcular_retorno() is the code created with macro generator of excel but is the one that i have to modify as the rows and columns in sheet Cotizaciones vary. I have to compute the log normal return of the price of the data available within Cotizaciones sheet and past the outomes in sheet Retornos from row B3, filling automatically down the rows and right the columns.
Furthermore, I have uploaded the add-in module named "VBA P" which is the project that i'm doing but is the "
Frankenstein"

many thanks, Davide

 

[Saurabhj]

Hi dnaddeo,

Seems you forgot to attach the excel file.

Thanks,
Saurabh

 

[dnaddeo]

Good afternoon,
I forwared before. Here again

 

[dnaddeo]

Foto attached

 

[Fluff]

How about

Sub dnaddeo()
   Dim UsdRws As Long, UsdCols As Long
   
   With Sheets("Cotizaciones")
      UsdRws = .Range("B" & Rows.count).End(xlUp).Row
      UsdCols = .Cells(1, Columns.count).End(xlToLeft).Column
   End With
   With Sheets("Retornos")
      .Range("B3:B" & UsdRws).Resize(, UsdCols - 1).FormulaR1C1 = "=LN(Cotizaciones!RC/Cotizaciones!R[-1]C)"
   End With
End Sub

 

[dnaddeo]

How about

Sub dnaddeo()
   Dim UsdRws As Long, UsdCols As Long
  
   With Sheets("Cotizaciones")
      UsdRws = .Range("B" & Rows.count).End(xlUp).Row
      UsdCols = .Cells(1, Columns.count).End(xlToLeft).Column
   End With
   With Sheets("Retornos")
      .Range("B3:B" & UsdRws).Resize(, UsdCols - 1).FormulaR1C1 = "=LN(Cotizaciones!RC/Cotizaciones!R[-1]C)"
   End With
End Sub

Hi Fluff first thanks for giving your time for helping me. I have copy/paste the code and run in. no error shown and is works well with dynamic row/column but as per photo just in column C, sheet Retornos the formula doesn't work . As you can seee for log normal formula the firt row 2 has to be blank.

 

[Fluff]

You need to change the format, it looks as though it's set to date.

 

[dnaddeo]

You need to change the format, it looks as though it's set to date.

Yes, I have checked and is work. Thanks again Fluff and as well thanks to Saurabhj for gived time for me

​