Iteration until one cell equals another
Posted by Jadie Tomlinson on October 11, 2001 5:04 AM
Cell A2 is a formula that contains, among other things, cell A1. How can I iterate A1 until A2 equals the contents of A3?
Solver and Goal Seek require that your target be a number but I want it to be the contents of a cell.
I tried to write a macro where A1 would increase its value if A2<>A3 but I must have not coded it correctly.
Any ideas?
Posted by david smith on October 11, 2001 12:40 PM
I've never been able to figure out why solver makes you look for a specific number too. Anyone have an answer.
Posted by Tim Francis-Wright on October 11, 2001 1:11 PM
Set, say A4 to be A2-A3.
Then you should be able to Goal Seek with
the goal of A4 to be zero and the changing
cell to be A1.
--Tim F-W
Posted by Dan on October 12, 2001 4:36 AM
Try this code. I'm assuming that you are dealing with integers and relatively small numbers.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
Do While Range("B1") < Range("C1")
Target(1).Value = Target(1).Value + 1
Loop
Do While Range("B1") > Range("C1")
Target(1).Value = Target(1).Value - 1
Loop
End If
Application.EnableEvents = True
End Sub
HTH
