Incremental filling of cells

M

mthnrd

I am having difficulty with VBA programming. I am writing a short VBA
function that performs an incremental math procedure. I have no
problem getting the function to work and converge to the desired
answer. However, I can't seem to get the function to return data after
each increment so I can see how modifications
to the program alter the rate of convergence.

For example, an algortithm calculating the square root of a number.
The loop is below:

Do Until Err < 0.000001
X = Num / Estimate
Err = Abs(Estimate - X)

'I need to return Estimate, Err, and iCounter for
'each iteration. For iteration 1, the values should
'go to cells [C1.E1]; iteration 2 to [C2.E2], and so on

Estimate = (X + Estimate) / 2
iCounter = iCounter + 1
Loop

Any hints/advice/help would be most gratefuly appreciated.

MthNrd
 
B

Bob Phillips

Do Until Err < 0.000001
X = Num / Estimate
Err = Abs(Estimate - X)

Cells(iCounter,"C").Value = Estimate
Cells(iCounter,"D").Value = Err
Cells(iCounter,"E").Value = iCounter

Estimate = (X + Estimate) / 2
iCounter = iCounter + 1
Loop


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
J

James Cox

You've run aground on the Excel principle that a FUNCTION can only return
values to the cell it is called from - and no other cells.

A SUBROUTINE can write values to as many cells as you need.

HTH,

James Cox
 
M

mthnrd

Bob,

Thanks that helped...I also found looking through other posts the
following code which appears to do the same:

Range("C" & iCounter) = Estimate
Range("D" & iCounter) = Err
Range("E" & iCounter) = iCounter

What is the difference?

Thanks again,

MthNrd
 
Top