# VB How to work out iterations

#### Charlie

##### Board Regular
How would I go about coding an app to work out how many iterations it would take to reduce a user entered number to one.

Im looking to divide the number by / 2 if the number is even.

If the number is odd im looking to use * 3 + 1.

Charlie

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Russell Hauf

##### MrExcel MVP
On 2002-10-26 12:42, Charlie wrote:
How would I go about coding an app to work out how many iterations it would take to reduce a user entered number to one.

Im looking to divide the number by / 2 if the number is even.

If the number is odd im looking to use * 3 + 1.

Charlie

Here are a couple of ways to do it. The first one prompts the user for the number and then tells them the number of iterations. The second one uses recursion and can be used as a worksheet formula. You can call it like this:

=NUMITER2(33)

Of course, you can also change the first one to be a worksheet function if you like.

<pre><font color='#000000'>
<font color='#000080'>Sub</font> NumIterations()
<font color='#000080'>Dim</font> lngNum <font color='#000080'>As</font> <font color='#000080'>Long</font>
<font color='#000080'>Dim</font> lngIterations <font color='#000080'>As</font> <font color='#000080'>Long</font>

lngNum = Application.InputBox(Prompt:="Please enter a number", _
Title:="My Title", Type:=1)

<font color='#000080'>Do</font> <font color='#000080'>While</font> lngNum > 1
<font color='#000080'>If</font> (lngNum And 1) = 1 <font color='#000080'>Then</font>
lngNum = (lngNum * 3) + 1
<font color='#000080'>Else</font>
lngNum = lngNum / 2
<font color='#000080'>End</font> <font color='#000080'>If</font>
lngIterations = lngIterations + 1
<font color='#000080'>Loop</font>

MsgBox "The number you entered took " & lngIterations & _
" iterations to get to 1."

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

<font color='#000080'>Function</font> NumIter2(lngNum <font color='#000080'>As</font> Long) <font color='#000080'>As</font> <font color='#000080'>Long</font>
<font color='#000080'>If</font> (lngNum And 1) = 1 <font color='#000080'>Then</font>
lngNum = (lngNum * 3) + 1
<font color='#000080'>Else</font>
lngNum = lngNum / 2
<font color='#000080'>End</font> <font color='#000080'>If</font>

<font color='#000080'>If</font> lngNum = 1 <font color='#000080'>Then</font>
NumIter2 = 1
<font color='#000080'>Else</font>
NumIter2 = NumIter2(lngNum) + 1
<font color='#000080'>End</font> <font color='#000080'>If</font>
<font color='#000080'>End</font> <font color='#000080'>Function</font>

</font></pre>

Hope this helps,

Russell

#### Charlie

##### Board Regular
Yeah you've given me something to think about. I was just looking at an example loop in "Sam's teach yourself VB 6 " it is similar to one of your examples.

Muchus Gracias
Charlie

Replies
12
Views
273
Replies
3
Views
243
Replies
1
Views
216
Replies
2
Views
158
Replies
4
Views
134

1,186,327
Messages
5,957,229
Members
438,294
Latest member
Soikeo79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back