Add sequential numbers for the column and text + numbers on rows

dnaddeo

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

I would have your support. I have a code that compute simulacions with random variables of a stock portfolio choosing how many days and how may simulations. Here the code that, after computing average and volatility of a sort of data, run the simulation and after the simulation i have create a code that create the graph. The problem is that, on the page of Simulciones I would add:
point 1- on column A the day number as 1,2,3 -> A2=day 1, A3=day 2, A4= day 3 and so on (highlighted in yellow in the photo)
point 2- on row A1 the name Date
point 3-from row B2= Simulaciones 1, C2= Simulaciones 2 and so on (highlighted in yellow in the photo)

So i would help specially for point 1 and point 3 becasue when i run the code for graph generatin, after adding the code created for days, the code select only the column B2:B

Here the code of random simulation
VBA Code:
    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)

Here the code for graph generation
VBA Code:
Option Explicit
' macro para crear grafico en nuevo book
Sub AddGrafico()

Dim lastrow As Long, lastcolumn As Long
    lastrow = Worksheets("Simulaciones").Cells(Rows.Count, 1).End(xlUp).Row
    lastcolumn = Worksheets("Simulaciones").Cells(1, Columns.Count).End(xlToLeft).Column
    Worksheets("Simulaciones").Range("A1", Cells(lastrow, lastcolumn)).Select

'crear y mostrar grafico
    ActiveSheet.Shapes.AddChart2(227, xlLineMarkers).Select
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Portfolio Return"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Portfolio Return"
    ActiveChart.Axes(xlCategory).Select
    Selection.TickLabelPosition = xlLow
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Grafico"
    Sheets("Grafico").Select
    Sheets("Grafico").Move After:=Sheets(Sheets.Count)
End Sub

Here the code for days created and is missing the simulaciones one
VBA Code:
Sub Dias()



Dim ws As Worksheet

Dim lastRow As Long



Set ws = Worksheets("Simulaciones")

Worksheets("Simulaciones").Activate

Rows("1:1").Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Columns("A:A").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove



lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

ws.Range("A2:A" & lastRow).Formula = "=row()-1"



End Sub


Thanks to all, Davide
 

Attachments

  • 1.png
    1.png
    58 KB · Views: 3
  • 2.png
    2.png
    75 KB · Views: 3

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,123,197
Messages
5,600,272
Members
414,374
Latest member
akbir

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