# VBa -useform dynamic - stock portolio weight

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

End Sub

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

Worksheets("Descriptiva").Activate
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
Else
MsgBox ("pesos entre 0 y 1")
Exit Sub
End If

' media y voltilidad de la cartera
Cells(12, 1) = "Cartera"
Cells(13, 1) = "Media"
Range("B13").Select
ActiveCell.FormulaR1C1 = "=TRANSPOSE(R[-3]C:R[-3]C[9])*(R[-6]C:R[-6]C[9])"
Range("B14").Select
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
Worksheets("Cotizaciones").Activate
lastcolumn = Worksheets("Cotizaciones").Cells(1, Columns.Count).End(xlToLeft).Column

Worksheets("Cotizaciones").Range("B1", Cells(1, lastcolumn)).Copy
Sheets("Simulaciones").Select
Range("A1").Select
ActiveSheet.Paste

End Sub

Private Sub UserForm_Initialize()
Worksheets("Descriptiva").Activate
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

#### Attachments

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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Replies
3
Views
227
Replies
10
Views
601
Replies
6
Views
58
Replies
20
Views
444
Replies
0
Views
119

1,128,107
Messages
5,628,724
Members
416,333
Latest member
Time2Learn

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