Question
I'm trying to create a macro that asks for a loan between 1 dollar and 5 million, and returns different interest rates based on the amount of the loan. For some reason when I type in different loan amounts it automatically makes my rate ".1", when it should be say ".08", or ".11" because of the entered loan amount.
Heres my code so far:
Sub DetermineinterestIf()
Dim loan As Variant
Dim interest As Double
Do
loan = InputBox("Enter your desired loan amount, from 1 dollar to 5 million dollars.")
Loop Until IsNumeric(loan)
If loan < 1000000 Then interest = ".08"
If loan > 4000000 Then interest = ".11"
If 1000000 < loan < 4000000 Then interest = ".10"
MsgBox ("The loan amount was " & loan & " with an interest rate of " & interest & ".")
End Sub
Any ideas where I have gone wrong?
Heres my code so far:
Sub DetermineinterestIf()
Dim loan As Variant
Dim interest As Double
Do
loan = InputBox("Enter your desired loan amount, from 1 dollar to 5 million dollars.")
Loop Until IsNumeric(loan)
If loan < 1000000 Then interest = ".08"
If loan > 4000000 Then interest = ".11"
If 1000000 < loan < 4000000 Then interest = ".10"
MsgBox ("The loan amount was " & loan & " with an interest rate of " & interest & ".")
End Sub
Any ideas where I have gone wrong?
Answers
First off, why use strings to specify a numeric (Double) value for interest?
I learned VBA a while ago, but I think that
If 1000000 < loan < 4000000 Then interest = .10
is invalid syntax. Try
If (1000000 < loan) and (loan < 4000000) Then interest = .10
Or, you might try
interest = 0.10
If loan < 1000000 Then interest = .08
If loan > 4000000 Then interest = .11
That is, assume the interest will be .10, and only change it if the loan amount is too big or too small
Or, if you find that you have to do other stuff depending on the interest rate, you could go whole-hog and say
If loan < 1000000 Then
interest = .08
do 8% stuff
Else If loan > 4000000 Then
interest = .11
do 11% stuff
Else
interest = .10
do 10% stuff
End If
I learned VBA a while ago, but I think that
If 1000000 < loan < 4000000 Then interest = .10
is invalid syntax. Try
If (1000000 < loan) and (loan < 4000000) Then interest = .10
Or, you might try
interest = 0.10
If loan < 1000000 Then interest = .08
If loan > 4000000 Then interest = .11
That is, assume the interest will be .10, and only change it if the loan amount is too big or too small
Or, if you find that you have to do other stuff depending on the interest rate, you could go whole-hog and say
If loan < 1000000 Then
interest = .08
do 8% stuff
Else If loan > 4000000 Then
interest = .11
do 11% stuff
Else
interest = .10
do 10% stuff
End If
Related Questions
You recently have found a location for your bakery and have started implementing the first phases of...
How to get isnumeric function to display an "error" message when necessary.
Sub Determineinterest...
The owner of a flower shop needs a short-term loan to tide her business over until she completes the...
You can afford a $1950 per month mortgage payment. You've found a 25 year loan at 6% interest.
Ro...