MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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
Do While Range("B1") > Range("C1")
Target(1).Value = Target(1).Value - 1
End If
Application.EnableEvents = True
End Sub