Can't get Loop to work

[matjusm]

I need some help with a relatively simply piece of VBA code.

Basically I'm working on a financial model with certian inputs and outputs.
Currently the NPV of the project is negative but I want to create a Macro that keeps adding 1€ to the price of the output until the NPV is above 0.

However the code I've got now just keeps looping endlessly and doesn't stop when the NPV reaches a positive value.

This is my code:

Sub Macro1()
'
' Macro1 Macro
' Increases price until NPV is positive'
'
Do

Range("F67").Select
Selection.Copy
Range("B67").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Loop Until B43 > 0

End Sub

Explanations:
B67= Price that feeds into the model
F67= B67+1
B43= NPV

What am I doing wrong?

 

[Sektor]

You only endlessly copy in loop. I see no incrementing.

 

[Weaver]

Why would you want to do this in a loop?

If the value is negative, then just add the integer difference between it and 0

Say your value is in A1

if range("A1").value < 0 then range("A1").value + abs(int(range("A1").value

or in vb

Sub test()
    Dim i As Double
    i = Range("A1").Value
    If i < 0 Then Range("A1").Value = i + Abs(Round(i, 0))
End Sub

 

[Ruddles]

There's a glaring error that would show up immediately if you headed your code module with the compiler directive Option Explicit:-

[B][COLOR=red]Option Explicit[/COLOR][/B]
 
Sub Macro1()
'
' Macro1 Macro
' Increases price until NPV is positive'
'
' ...etc

 

[matjusm]

Got it to work: the problem was that I wrote the condition I wanted to be met incorrectly. I should have written:

Range("B43") > 0 not just B43 > 0

 

[Weaver]

I'm curious, why are you doing this using increments? Is there some other process you're not including in the code?

 

[matjusm]

I'm curious, why are you doing this using increments? Is there some other process you're not including in the code?

This doesn't have to be super accurate so 1€ increments are quite sufficient for me.

 

[Ruddles]

Got it to work: the problem was that I wrote the condition I wanted to be met incorrectly. I should have written:

Range("B43") > 0 not just B43 > 0

Did you discover that by adding Option Explicit to your module?

I recommend you do that to all your code modules - it can save you a lot of time and bother!

 

[Weaver]

This doesn't have to be super accurate so 1€ increments are quite sufficient for me.

Did you read my post regarding alternate methods?