Incremental filling of cells

  • Thread starter Thread starter mthnrd
  • Start date Start date
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
 
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)
 
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
 
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
 
Back
Top