Is this macro easy to write?

[excelos]

Hello!

Is it easy to write a macro that will fill a specific input cell with 0, 1, 2, 3, etc until the output cell becomes 0 (various formulas connect these two cells)

Thanks!

 

[mrshl9898]

Have you used Goal Seek before? Data > What if Analysis > Goal Seek. Sounds like what you need, or if it must be a macro this is what a recorded Goal Seek looks like:

Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1")

B1 had formula =A1*5

 

[excelos]

Have you used Goal Seek before? Data > What if Analysis > Goal Seek. Sounds like what you need, or if it must be a macro this is what a recorded Goal Seek looks like:

Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1")

B1 had formula =A1*5

Many thanks that is a great feature!
But the problem is that it does not allow me to specify criteria for the input cell.
Is that possible?

 

[steve the fish]

Heres a loop that will increment B1 by 1 until A1 equals 0. You can change the number of iterations if you want. Dont know if thats what you mean.

a = 0
Do Until Range("A1").Value = 0
    Range("B1").Value = a
    If a = 5000 Then 'max number of iterations to prevent infinite loop
        Range("B1").Value = ""
        MsgBox "Max iterations reached. Cant find the result"
        Exit Sub
    End If
    a = a + 1
Loop

 

[RoryA]

Could you stop posting essentially the same question repeatedly, please.

 

[excelos]

Could you stop posting essentially the same question repeatedly, please.

But I got much better answers this time!

 

[excelos]

Have you used Goal Seek before? Data > What if Analysis > Goal Seek. Sounds like what you need, or if it must be a macro this is what a recorded Goal Seek looks like:

Range("B1").GoalSeek Goal:=10, ChangingCell:=Range("A1")

B1 had formula =A1*5

That is great! Do you know how to specify additional criteria for A1?

 

[RoryA]

But I got much better answers this time!

That's really not the point.

 

[excelos]

That's really not the point.

That is great! Do you know how to specify additional criteria for A1?

Can you help me with this please?

 

[mrshl9898]

Depends what you want to do. Goal seek just finds a number needed to solve an equation where the answer is known.

You could use goalseek only IF criteria is met.

If Range("A1") = 'some scenarios' then
'goalseek code
End if