Select a range by using a input box as a number

[viomman]

I am new to the forum and a novice excel vba.
Can any one give me a example code to select a range of years(each year in the cells I.E. 2009-2050) by selecting a number of years (i.e. 20 year evaluation) and then use that range in the formula =NPV('Green Forcasting'!G7,F74:AC75)+E74 locaten in one cell . I want the user to be promted by a input box
Set rng =application.inputbox(promt:="Enter number of years for NPV evaluation", Type:=1)
PLEASE HELP I am a little lost as to the direction to take!!

[Kenneth Hobs]

What part of that formula comes from the inputbox? Set your Type=9 and it will allow the user to pick the range by dragging.

[mdmackillop]

This assumes you want to vary F74:AC75 to suit the number of years
[vba]
Sub MyNPV()
Dim x As Long
x = InputBox("Enter number of years for NPV evaluation")
ActiveCell.Formula = _
"=NPV('Green Forcasting'!G7," & Range("F74").Resize(2, x).Address & ")+E74"
End Sub

[/vba]

[viomman]

Thank you both this soled my dilema not that it was a big one. Ill be here more often!!!! Thanks again

[lucas]

Mark your thread solved using the thread tools at the top of the page please.

You don't say how you solved it.......would you share it with us?

[viomman]

This is how I solved it thanks to mdmackillop
Sub MyNPV()
Dim x As Long
x = InputBox("Enter number of years for NPV evaluation")
Sheets("PV Green Estimating").Select
Range("E85").Select
ActiveCell.Formula = _
"=NPV('Green Forcasting'!G7," & Range("F82").Resize(2, x).Address & ")+E74"
Range("E86").Select
ActiveCell.Formula = _
"=NPV('Green Forcasting'!G7," & Range("F83").Resize(1, x).Address & ")+E74"
End Sub

I will cal this sub as needed in my userforms to utilize the NPV calculations.

[mdmackillop]

It's more efficient to set values etc. directly without selecting worksheets/cells. You can refer to them directly.
In this case
[VBA]Sub MyNPV()
Dim x As Long
x = InputBox("Enter number of years for NPV evaluation")
With Sheets("PV Green Estimating")
.Range("E85").Formula = _
"=NPV('Green Forcasting'!G7," & Range("F82").Resize(2, x).Address & ")+E74"
.Range("E86").Formula = _
"=NPV('Green Forcasting'!G7," & Range("F83").Resize(1, x).Address & ")+E74"
End With
End Sub
[/VBA]

[viomman]

Thank you
that is why i am a novice at vba

[mdmackillop]

Best to start on the right track then!

[viomman]

Thanks I really appreciate this. I will be using this a lot

[viomman]

Can you tell me how to handle the debug error when the cancel button is used in the input box

[mdmackillop]

Cancel is equivalent to leaving the box blank. Step through this, trying both options

[VBA]Sub Test()
x = InputBox("Enter number of years for NPV evaluation")
If x = "" Then Exit Sub
MsgBox x
End Sub[/VBA]

[viomman]

I keep getting a type mimatch on this line when cancel is used or left blank.
xRng = InputBox("Enter number of years for NPV evaluation")
any thoughts?

[viomman]

This is how I addressed the debug error
Sub MyNPV()
Dim xRng As Long
On Error Resume Next
xRng = InputBox("Enter number of years for NPV evaluation")
If xRng = 0 Or xRng = vbNull Then
Exit Sub
MsgBox ("you have not entered a value for the evaluation period"), vbCritical
Else
With Sheets("LCCACalculations")
.Range("E85").Formula = _
"=NPV('LCCAParameters'!G7," & Range("F82").Resize(1, xRng - 1).Address & ")+E82"
.Range("E86").Formula = _
"=NPV('LCCAParameters'!G7," & Range("F83").Resize(1, xRng - 1).Address & ")"
End With
End If
End Sub

[mdmackillop]

You need to swap these lines
[vba]Exit Sub
MsgBox ("you have not entered a value for the evaluation period"), vbCritical
[/vba]