PDA

View Full Version : Selecting a range for a barchart in vba



viomman
12-02-2010, 07:48 AM
I have a sub routine that is giveing me some trouble mabey one of you guys can give me some direction. I am trying to make the data selection criteria dynamic and attached to a input box. the problem is in the chart macro and I want the selection criteria to attach to the yRng. the problem code is in red

here is my code


Sub MyNPV()
Dim xRng As Long
Dim yRng As Long
On Error Resume Next
yRng = InputBox("Enter the longest useful life between the assets being evaluated.")
xRng = InputBox("Enter number of years for NPV evaluation. Per the 2011 Financial Analysis Manual the default evaulation shall be 25 years. Please submit a deviation form included in the F.A.M 2011 for evaluations other than 25 yrs.")
If xRng = 0 Or xRng = vbNull Then
MsgBox ("you have not entered a value for the evaluation period"), vbCritical
BusCaseDshBrd.Show
Exit Sub
Else
Sheets("LCCACalculations").Select
Call UnprotectAll
With Sheets("LCCACalculations")
.Range("E127").Formula = _
"=If(NPV('LCCAParameters'!G7,E113:" & Range("E113").Offset(0, yRng - 1).Address & ")=0,0,(NPV('LCCAParameters'!G7,E113:" & Range("E113").Offset(0, yRng - 1).Address & ")))"
.Range("E123").Formula = _
"=NPV('LCCAParameters'!G7," & Range("F120").Resize(1, xRng - 1).Address & ")+E120+D123"
.Range("D113").Formula = _
"=If(E127*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)=0,0,(E127*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)))"
.Range("D114").Formula = _
"=If(NPV('LCCAParameters'!G7," & Range("E114").Offset(0, xRng - 1).Address & ":" & Range("E114").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)=0,0,(NPV('LCCAParameters'!G7," & Range("E114").Offset(0, xRng - 1).Address & ":" & Range("E114").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)))"
.Range("D112").Formula = _
"=If(NPV('LCCAParameters'!G7,E112:" & Range("E112").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)=0,0,(NPV('LCCAParameters'!G7,E112:" & Range("E112").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1))"
.Range("D116").Formula = _
"=If(NPV('LCCAParameters'!G7," & Range("E116").Offset(0, xRng - 1).Address & ":" & Range("E116").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)=0,0,(NPV('LCCAParameters'!G7," & Range("E116").Offset(0, xRng - 1).Address & ":" & Range("E116").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)))"
.Range("D111").Formula = _
"=If(NPV('LCCAParameters'!G7," & Range("E111").Offset(0, xRng - 1).Address & ":" & Range("E111").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)=0,0,(NPV('LCCAParameters'!G7," & Range("E111").Offset(0, xRng - 1).Address & ":" & Range("E111").Offset(0, yRng - 1).Address & ")*((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)/(((1+LCCAParameters!$G$6)^LCCAParameters!$D$72)-1)))"
End With
With Sheets("Project Summary Printout")
.Range("H37") = xRng
End With
Call ProtectAll
End If
'
' ChartMacro Macro
'
'
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Name = "TEMP SHEET"
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlBarStacked
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Tornado Chart"
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 2
ActiveChart.ClearToMatchStyle
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetSourceData Source:=Sheets("LCCACalculations").Range( _
"C110:CZ117")
ActiveChart.SeriesCollection(1).Values = "='LCCACalculations'!$E$110"
ActiveChart.SeriesCollection(2).Values = "='LCCACalculations'!$E$111:" & Range("E111").Offset(0, yRng - 1).Address & ""
ActiveChart.SeriesCollection(3).Values = "='LCCACalculations'!$E$112:" & Range("E112").Offset(0, yRng - 1).Address & ""
ActiveChart.SeriesCollection(4).Values = "='LCCACalculations'!$E$113:" & Range("E113").Offset(0, yRng - 1).Address & ""
ActiveChart.SeriesCollection(5).Values = "='LCCACalculations'!$E$114:" & Range("E114").Offset(0, yRng - 1).Address & ""
ActiveChart.SeriesCollection(6).Values = "='LCCACalculations'!$E$115:" & Range("E115").Offset(0, yRng - 1).Address & ""
ActiveChart.SeriesCollection(7).Values = "='LCCACalculations'!$E$116:" & Range("E116").Offset(0, yRng - 1).Address & ""
ActiveChart.SeriesCollection(8).Values = "='LCCACalculations'!$E$117:" & Range("E117").Offset(0, yRng - 1).Address & ""
ActiveChart.SeriesCollection(8).XValues = "='LCCACalculations'!$E$5:" & Range("E5").Offset(0, yRng).Address & ""
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Height = 372.236
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.Select
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Left = 409.088
Selection.Top = 522.195
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Left = 173.088
Selection.Width = 584.719
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Width = 783.719
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Height = 79.663
Selection.Top = 587.195
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Width = 1071.272
Selection.Height = 566.236
ActiveSheet.ChartObjects("Chart 1").Activate
Selection.Height = 542.236
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(1).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(3).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(5).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(7).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(2).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(4).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(6).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Legend.LegendEntries(8).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).TickLabelSpacing = 1
ActiveChart.Axes(xlCategory).TickLabelSpacing = 25
Selection.MajorTickMark = xlNone
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).HasMajorGridlines = True
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).MajorGridlines.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).TickMarkSpacing = 25
ActiveChart.Axes(xlCategory).TickLabelSpacing = 5
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).HasMinorGridlines = True
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).MinorGridlines.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlCategory).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).MajorUnit = 0.1
ActiveChart.Axes(xlValue).MajorUnit = 2000000
ActiveChart.Axes(xlValue).MinorUnit = 0.02
ActiveChart.Axes(xlValue).MinorUnit = 500000
Sheets("TEMP SHEET").Select
ActiveWindow.SelectedSheets.Delete
End Sub

viomman
12-02-2010, 01:43 PM
I am trying to get the sourcedata to be a dynamic address off of the xRng that the user puts into the input box can someone please help???????????????