Using input box data in macro calculation

[matjusm]

I've searched everywhere for this but I haven't found the answer. Should be an easy one for all you Excel pros out there.

Basically I got a financial model where I want to have a Macro that loops a certain action until a certain value that is specified with an input box is reached.

More specifically, I want this macro to add 1€ to the price of the output until the IRR of the project (which is automatically calculated in a different cell on the worksheet) is equal to or greater than the IRR specified by the user in the input box.

myIRR = InputBox("Enter your desired IRR") 
Do
    
    Range("B69").Select
    Selection.Copy
    Range("B67").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
   Loop Until Range("B43") > myIRR

Cell explanations:
B69= B67+1
B67= sales price
B43= IRR calculated from current cashflows

When I run the text as it is, the loop just continues endlessly but I want it to only continue until the actual IRR is larger than the one the user enters.

 

[Anthony47]

The correct syntax of your instructions is

Do Until Range("B43") > myIRR
'instructions
Loop

Or you just use Do /Loop but insert in the loop something like

If Range("B43") > myIRR then Exit Do

Bye

 

[Darkspartan]

The correct syntax of your instructions is

Do Until Range("B43") > myIRR
'instructions
Loop

Or you just use Do /Loop but insert in the loop something like

If Range("B43") > myIRR then Exit Do

Bye

I also am not sure if the code will ever exit the loop.
What controls B43?
If B43 =5 and myIRR = 10, nothing in this code will make B43 > 10; cause of the loop?

 

[Anthony47]

My understanding is that B43 contains an incrementing value to be compared with the irr set via inputbox.
But you are right: if nothing changes B43 the macro will loop endless.

Maybe matjusm should better specify on which portion of his problem he needs help.

Bye

 

[matjusm]

I also am not sure if the code will ever exit the loop.
What controls B43?
If B43 =5 and myIRR = 10, nothing in this code will make B43 > 10; cause of the loop?

My understanding is that B43 contains an incrementing value to be compared with the irr set via inputbox.
But you are right: if nothing changes B43 the macro will loop endless.

Maybe matjusm should better specify on which portion of his problem he needs help.

Bye

B43 is controlled by B67 which in turn gets its value from B69 and I want Excel to keep copying B69 into B67 until B43 is greater than a value entered by the user.

 

[Weaver]

I don't understand why you'd want to use a loop for this, when the answer can be easily calculated.

 

[matjusm]

I don't understand why you'd want to use a loop for this, when the answer can be easily calculated.

I'm still a beginner in Macros, hence I can't think of a way to calculate it.

Can you teach me?

Regardless, I would like to find out how can I use an input box to specify a condition that I want met until which a looping process must run.

 

[Darkspartan]

I'm still a beginner in Macros, hence I can't think of a way to calculate it.

Can you teach me?

Regardless, I would like to find out how can I use an input box to specify a condition that I want met until which a looping process must run.

Does B43 =B67?
Earlier I thought you said B43 was calculated from other items on the spreadsheet?

 

[matjusm]

Does B43 =B67?
Earlier I thought you said B43 was calculated from other items on the spreadsheet?

No, the two don't equal each other.

B43 calculates itself off of cells which get their input from B67. What I want my macro to do (and this far I have succeeded) is to copy and then paste values in cell B69 which is equal to B67+1 (basically bumping up the price in 1€ increments) until B43 is greater than a user specified number which the person enters into the input box. Its this last part of getting the input box number into the macro so that it can be used as an input figure that I'm having trouble with.

Unfortunately posting the original file is not an option so I made a quick copy that illustrates what I'm trying to do:

http://www.easy-share.com/1916638093/Sample.xlsm

The setup is exactly the same, just the details are different in this file:
Rows 2-3- cash inflows and outflows, inflows dependent on price in B13.
B16 is a cell that adds 0,2 to the price and I want the macro loop to copy and paste this value into B13 until certain conditions are met.
There are three macros in this workbook:
1. Resetprices (to make things easier for you)
2. NPVmacro (which works just fine and copy and pastes prices into B13 until the NPV is positive.
and finally the troublesome one:
3. IRRmacro: it opens up a text box which asks the user to enter a desired IRR and is then supposed to work just like the NPV macro by copy and pasting prices into B13 until the value in B9 (B6*100 since you're entering a full number, not a fraction into the macro input box) is greater than the value entered by the user. However this doesn't work and the macro loops endlessly and I have to press escape twice to get out.

Can someone point out what I'm doing wrong?

 

[Anthony47]

The main problem with your code is that an input box returns a string, not a number, thus your number will never be greater than a string.
Just use

myIRR = Val(InputBox("Enter your desired IRR"))

Vba for versions higher than xl2003 will accept the Do / Loop Until syntax that you used and initially I guessed was the problem.

Bye