PDA

View Full Version : Select a range by using a input box as a number

viomman
02-05-2009, 12:01 PM
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)

Kenneth Hobs
02-05-2009, 12:17 PM
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
02-05-2009, 12:20 PM
This assumes you want to vary F74:AC75 to suit the number of years

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

viomman
02-05-2009, 12:27 PM
Thank you both this soled my dilema not that it was a big one. Ill be here more often!!!! Thanks again

lucas
02-05-2009, 12:31 PM

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

viomman
02-05-2009, 01:02 PM
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
02-05-2009, 01:22 PM
It's more efficient to set values etc. directly without selecting worksheets/cells. You can refer to them directly.
In this case
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

viomman
02-05-2009, 01:52 PM
Thank you
that is why i am a novice at vba

mdmackillop
02-05-2009, 02:13 PM
Best to start on the right track then!

viomman
02-05-2009, 02:14 PM
Thanks I really appreciate this. I will be using this a lot

viomman
02-05-2009, 02:58 PM
Can you tell me how to handle the debug error when the cancel button is used in the input box

mdmackillop
02-05-2009, 03:14 PM
Cancel is equivalent to leaving the box blank. Step through this, trying both options

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

viomman
02-06-2009, 07:09 AM
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
02-06-2009, 07:30 AM
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
02-06-2009, 08:40 AM
You need to swap these lines
Exit Sub
MsgBox ("you have not entered a value for the evaluation period"), vbCritical