VBa -useform dynamic - stock portolio weight


New Member
Dec 29, 2020
Office Version
  1. 2019
  2. 2016
  3. 2013
Dear all,
food year first. I have an issue for the following code. As per photo 1.2 i have create an useform where upload the % of a portfolio allocation (from 0,1 to 1%) for a max of 10 stock and in case put 0 as number if that stock doesn'twant be choosen from the user. The problem is that i need to be dynamic as, in case of add 0 to useform, for my code is adding as well a number and it's count it. (i have highlighted in red, column H10-K10). The second issue is on photo 1.1 as i should compute the calcolation with "volatilidad anual" and no "media anual". As you can see the transport is because the calcolation is run on matric, addiung moltiplin with the value add from the usedfrom.
Here the code:

VBA Code:
Private Sub Cancelar_Click()
    Unload Formulario

End Sub

Private Sub Simular_Click()
'guardar los pesos en la pestana Descriptivos

    Cells(10, 1).Value = "Pesos"
'notificar si el numero es numero o no
If IsNumeric(Peso_01.Value) And IsNumeric(Peso_02.Value) And IsNumeric(Peso_03.Value) And IsNumeric(Peso_04.Value) _
    And IsNumeric(Peso_05.Value) And IsNumeric(Peso_06.Value) And IsNumeric(Peso_07.Value) And IsNumeric(Peso_08.Value) And IsNumeric(Peso_09.Value) _
    And IsNumeric(Peso_10.Value) Then
    Cells(10, 2).Value = Peso_01.Value
    Cells(10, 3).Value = Peso_02.Value
    Cells(10, 4).Value = Peso_03.Value
    Cells(10, 5).Value = Peso_04.Value
    Cells(10, 6).Value = Peso_05.Value
    Cells(10, 7).Value = Peso_06.Value
    Cells(10, 8).Value = Peso_07.Value
    Cells(10, 9).Value = Peso_08.Value
    Cells(10, 10).Value = Peso_09.Value
    Cells(10, 11).Value = Peso_10.Value
    MsgBox ("pesos entre 0 y 1")
    Exit Sub
End If

' media y voltilidad de la cartera
    Cells(12, 1) = "Cartera"
    Cells(13, 1) = "Media"
    Cells(14, 1) = "Volatilidad"
    ActiveCell.FormulaR1C1 = "=TRANSPOSE(R[-3]C:R[-3]C[9])*(R[-6]C:R[-6]C[9])"
    ActiveCell.FormulaR1C1 = "=TRANSPOSE(R[-4]C:R[-4]C[9])*(R[-7]C:R[-7]C[9])"

' simluar 10 acciones en cartera
     Application.Run "ATPVBAEN.XLAM!Random", "Simulaciones", N_sim.Value, Dias.Value, 2, , _
       Cells(13, 2).Value, Cells(14, 2).Value
       Worksheets("Simulaciones").Move After:=Worksheets(Worksheets.Count)

'copiar nombre titulos
   ' Sheets("Cotizaciones").Select
   ' Range("B1:K1").Select
   ' Selection.Copy
   ' Sheets("Simulaciones").Select
   ' Range("A1").Select
   ' ActiveSheet.Paste
    Dim lastcolumn As Long
    lastcolumn = Worksheets("Cotizaciones").Cells(1, Columns.Count).End(xlToLeft).Column

    Worksheets("Cotizaciones").Range("B1", Cells(1, lastcolumn)).Copy

Unload Formulario

End Sub

'para anadir nombre acciones
Private Sub UserForm_Initialize()
    Label1.Caption = Cells(1, 2).Value
    Label2.Caption = Cells(1, 3).Value
    Label3.Caption = Cells(1, 4).Value
    Label4.Caption = Cells(1, 5).Value
    Label5.Caption = Cells(1, 6).Value
    Label6.Caption = Cells(1, 7).Value
    Label9.Caption = Cells(1, 8).Value
    Label10.Caption = Cells(1, 9).Value
    Label11.Caption = Cells(1, 10).Value
    Label12.Caption = Cells(1, 11).Value

End Sub

Thanks to all that can check it, Davide


  • 1.1.png
    122.8 KB · Views: 5
  • 1.2.png
    72.3 KB · Views: 5
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Latest member

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