Anyone an expert or able to help? I need to do a basic iteration to get one cell to equal 0 by changing the value in another cell in small increments? Cheers

Not a guru by any means,

Sub enter_zero()

Range(“B2”).Value = 0

End Sub

Sub looper()
While Sheet2.Range(“a1”) <> 0
Sheet2.Range(“b1”).Value = Sheet2.Range(“b1”).Value + 1
Wend
End Sub

Sorry Albanach,

Just read your post properly but not sure what your after, could you explain a little further?

Sounds like you need “goal seek” under Tools-Goal Seek if you only need to change one cell to set the other to zero.

E.g. Set cell: “B1” To Value: 0. By changing cell: “A1”,
where B1 contains a formula which is a function of A1.

UDL -0.70949497 N/mm DUTY WITH NEW CLEARANCES

Ref Ref Len. Loads (N) Reactions BM

1 0 -613.125 0 0
2 128 0 0 -84292
3 268 0 2267.9 -189797
4 338 0 0 -89011
5 434 0 0 43556
6 556.1 0 0 202719
7 677.5 0 0 350482
8 774.25 0 0 460754
9 871 0 0 564386
10 967.75 0 523.6 661376
11 1010.5 -1494.65 0 724501
12 1044.5 0 526.58 722961
13 1082 0 0 740059
14 1092.75 0 660.52 744776
15 1135.5 -1494.65 0 790961
16 1169.5 0 610.71 775950
17 1207 0 0 781343
18 1217.75 0 733.91 782705
19 1260.5 -1494.65 0 818685
20 1294.5 0 639.21 795557
21 1332 0 0 793068
22 1342.75 0 741.79 792170
23 1385.5 -1494.65 0 819500
24 1419.5 0 611.37 789493
25 1457 0 0 778372
26 1467.75 0 684.1 775000
27 1510.5 -1494.65 0 790024
28 1544.5 0 528.43 750229
29 1582 0 0 725202
30 1592.75 0 563.5 717844
31 1635.5 -1494.65 0 711860
32 1669.5 0 394.79 655358
33 1707 0 0 606892
34 1717.75 0 387.02 592814
35 1760.5 -1494.65 0 552565
36 1832 0 0 375483
37 1895.5 -421.83 2635.4 215173
38 2017 0 0 169413
39 2158.134 0 0 103110
40 2299.268 0 0 22673
41 2400.5 0 0 -43725
42 2471 0 1057.8 -94262
43 2572.75 0 0 -65785
44 2674 0 0 -44740
45 2716 226.611 0 -38144
46 2783.5 0 0 -14870
47 2800 0 0 -9673
48 2810 0 0 -6617
49 2830 0 0 -717
50 2832.5 0 0 -0.007701032

That didn’t quite work out how I planned it but essentially the figure -0.007701032 is affected by changing the value in bold at the top, this cell is a sum of certain values.

Just now I have to manually change the UDL figure at the top to get the bold figure at the bottom to be as close to 0 as possible.

hope this helps explain

Oh yeah and the top bold figure is in Cell C1 and the bottom is in E54…

Read my post.

Where can i send the invoice?

Don’t have goal seek under tools I’m afraid?

Then it gets complicated and i really would send you invoice 😛

Display to 1 decimal point. Problem solved.

So you dont want it to actually equal 0?

Im confused.

Have a look under Tools, Add-Ins, see if you have “Solver Add-in” available. Think that is where goal seek hides.

goal seek would be best but soma_rich’s loop gets you close to what you need. You need to catch the loop going beneath zero and iterate the other way with a smaller value until it goes over zero, then again with a smaller value until it goes beneath zero etc. Stop the whole lot running when it gets to (x > -0.0001) or (x < 0.0001).
Or something like that!

Are you using excel? What version are you using?

Something like this?

Sub looper()
if Sheet2.Range(“a1”) < 0 then
While Sheet2.Range(“a1”) <> 0
Sheet2.Range(“b1”).Value = Sheet2.Range(“b1”).Value + 0.00001
Wend
else
While Sheet2.Range(“a1”) <> 0
Sheet2.Range(“b1”).Value = Sheet2.Range(“b1”).Value – 0.00001
Wend
end if
End Sub

What KennyNI said. You need to install the Goal Seek add-in. Takes 2 minutes.

So you dont want it to actually equal 0

Can be between -0.01 and 0.01.

Are you using excel? What version are you using?

Yeah using Excel, 2000 SR-1 Standard.

If the cell required to get to as close to zero as possible contains a formula, in this case SUM(F54:BD54), will this affect the goal seek? Which I now have, thanks Kenny!

Actually, I think it will – Excel will either refuse to cooperate or will over-write your formula. Not sure exactly what you’re trying to calculate here (mechanical engineer in a former life) but seems like you’re going about it a bit backwards!

Its a bending moment calculation on a shaft with different loads and reactions on it, trying to set the value to zero which effectively means the shaft is locked in position on te rhs.

Thanks for the heads up on the goal seek.

If the cell required to get to as close to zero as possible contains a formula, in this case SUM(F54:BD54), will this affect the goal seek?

How would goal seek work unless the cell required to get as close to zero didn’t contain a formula?

Goal seek won’t change the formula in that cell, it’ll ask you which cell should be zero (e54) and which cell needs a number changing in order to make that zero (c1) and then change the number in that cell (c1)