Autofill dynamic range formula - Stock price ln return

dnaddeo

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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
414
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello Davide,

Kindly share the spreadsheet having data.

Thanks,
Saurabh
 

dnaddeo

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

Active Member
Joined
Jun 6, 2020
Messages
414
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi dnaddeo,

Seems you forgot to attach the excel file.

Thanks,
Saurabh
 

dnaddeo

New Member
Joined
Dec 29, 2020
Messages
11
Office Version
  1. 2019
  2. 2016
  3. 2013

ADVERTISEMENT

Good afternoon,
I forwared before. Here again :)
 

dnaddeo

New Member
Joined
Dec 29, 2020
Messages
11
Office Version
  1. 2019
  2. 2016
  3. 2013
Foto attached
 

Attachments

  • excel.png
    excel.png
    104.1 KB · Views: 9

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
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
 
Solution

dnaddeo

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

Attachments

  • sheet 1.png
    sheet 1.png
    66.4 KB · Views: 7
  • sheet2.png
    sheet2.png
    68.6 KB · Views: 7

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
You need to change the format, it looks as though it's set to date.
 

dnaddeo

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

 

Watch MrExcel Video

Forum statistics

Threads
1,130,122
Messages
5,640,241
Members
417,131
Latest member
Seanr19871

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