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.

 

[mikerickson]

Do / Loop Until works in Excel 2011.

I join Weaver in recomending a non-loop solution.

What is the formula in B43?

 

[Anthony47]

Don't miss comments from mikerickson and Weaver about a non-loop approach to the problem.

You should have your loop working using, as suggested a couple of posts ago:

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

Bye

 

[matjusm]

Do / Loop Until works in Excel 2011.

I join Weaver in recomending a non-loop solution.

What is the formula in B43?

Formula in B43 is a standard Excel IRR formula.

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

I tried that like this:

Sub
 myIRR = Val(InputBox("Enter desired IRR"))
    Do
    
    Range("B71").Select
    Selection.Copy
    Range("B69").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
   Loop Until Range("B73") > myIRR
   
End Sub

However when I run the macro and enter the desired number, it gives me an error message: Run-time error 13: Type mismatch. When I click on Debug, it has highlighted in yellow Loop Until Range("B73") > myIRR

Am I missing some piece of code that tells Excel that myIRR actually is referring to the number that the user entered?

 

[Anthony47]

I tested the instruction with your published workbook, in Excel 2010, so please start playing with the same test bed.

I guess that B73 is the equivalent of B9 in the published workbook, thus it contains a formula like =B6*100. Anyway make sure B73 is a "number" not a string.
Also, do you "Dim" the variable MyIRR? If YES, make sure it is a numeric entity.
When in debug mode, set the cursor on "Range" (in Range("B73")> etc; do not click into Range) and look which value will be shown after a few seconds, then repeat with myIRR; check that no " (I don't remember which is the english name of this symbol) surround the shown values.
Finally, try running the macro step by step and check if the error arise at your first cycle or after many cycles, and you may look at the progress of the data (and even change them) into the worksheet: set the cursor into the body of the macro (click somewhere), F8; you will execute one line at each F8 pressing.

Also, please replace all your instructions in the loop with this one:

    Range("B13").Value = Range("B16").Value

Bye