Solved Looking for an Excel macro/VBA loop

May 10, 2012 at 11:29:24
Specs: Windows XP
I have a spreadsheet where I calculate several numbers but then require the user to enter a number until an allowable result is reached. How can I generate the correct number rather than require user input.

In my example D13=D11-D12 where D13 must be greater than zero.
G5 is the user input cell.
How do I get G5 to increment from 1 to ... until D13 is greater than zero?

Thanks,


See More: Looking for an Excel macro/VBA loop

Report •


✔ Best Answer
May 11, 2012 at 07:46:24
Here's the problem I'm having...

You appear to have 2 user input cells, E5 and D12.

You have asked for a way to increment E5 automatically until D13 > 0

My concern is that the manual entry in D12 could be such that D13 will never be > 0. I guess that's something that you will need to deal with.

That said, are you aware of the Solver feature?

Solver can be found under Tools. If it's not there, you'll need to add it via Tools...Add-ins. With the Solver, you can set up models and have Excel try to find the value in a given cell that "solves" your model. That might be better than using a macro.

If all you want is a macro, then this code will increment E5 by 1 each time through the loop and keep checking D13 until it is greater than 0.

As written, it only tries from 1 to 100, but that is easily changed.

Sub Check_D13()
 For myNum = 1 To 100
  Range("E5") = myNum
   If Range("D13") > 0 Then
    MsgBox "D13 equals " & Range("D13")
    Exit Sub
   End If
 Next
 MsgBox "No Solution Found"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 10, 2012 at 11:31:25
ps. I don't know VBA and I'm working in Excel 2003

Report •

#2
May 10, 2012 at 11:39:45
Something is missing in your post.

The user is changing G5, yet the formula in D13 doesn't even reference G5.

Unless D11 or D12 reference G5, no change in G5 is going to impact D13.

If D11 or D12 do reference G5, tell us how.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
May 10, 2012 at 15:51:24
Thank you for your comment. In trying to simplify the explaination of my problem I confused you.

E5 and D12 are numbers input by the user

The contents of E9 references G5 as shown below:

D9=5*D5*(E5*12)^3/1000/1000
E9=384*F5*G5/1000/1000

D11=D9/E9
D12=(E5*12)/G12

D13=D12-D11 which needs to be greater than zero

I think I've added all the pertinent formulae so hopefully this makes more sense to you.


Report •

Related Solutions

#4
May 10, 2012 at 17:59:40
E5 and D12 are numbers input by the user

D12=(E5*12)/G12

How can D12 be both "user input" and contain a formula?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
May 11, 2012 at 03:35:36
It's becoming apparent why I can't solve this problem...I mislabeled in my last post, please consider D12 as user input. Thanks again.

Report •

#6
May 11, 2012 at 07:46:24
✔ Best Answer
Here's the problem I'm having...

You appear to have 2 user input cells, E5 and D12.

You have asked for a way to increment E5 automatically until D13 > 0

My concern is that the manual entry in D12 could be such that D13 will never be > 0. I guess that's something that you will need to deal with.

That said, are you aware of the Solver feature?

Solver can be found under Tools. If it's not there, you'll need to add it via Tools...Add-ins. With the Solver, you can set up models and have Excel try to find the value in a given cell that "solves" your model. That might be better than using a macro.

If all you want is a macro, then this code will increment E5 by 1 each time through the loop and keep checking D13 until it is greater than 0.

As written, it only tries from 1 to 100, but that is easily changed.

Sub Check_D13()
 For myNum = 1 To 100
  Range("E5") = myNum
   If Range("D13") > 0 Then
    MsgBox "D13 equals " & Range("D13")
    Exit Sub
   End If
 Next
 MsgBox "No Solution Found"
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
May 11, 2012 at 18:39:01
Thank you for your input...I am aware of Solver but it doesn't work for this case...your solution also does not solve my problem unfortunately...if you care to PM me I can send you the .xls file but you've done more than enough and again - thanks!

Report •

#8
May 11, 2012 at 18:49:51
It's possible that the code does not work because you keep changing your requirements.

In your first post you said that you wanted to increment G5 but in Post 3 you changed the input cell to E5.

I wrote the code to increment E5 since that was the latest cell you mentioned.

Without clear requirements it's kind of hard to ensure that the solutions we offer will actually work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question