Hi Stefi -

I would like to use the For loop in the macro to determine the formula. The
formula will have some parts relative to the cell it is being put in, and
other parts absolute. I can't figure out the relative parts using the
counters (k & l) which allow it to appear as a formula in excel spreadsheet.

As for how it appears in the spreadsheet - i expect it will appear as A1/B1
style. I'm not worried if it uses $A$1 style for the spreadsheet (although
that might be good to know as well) because the macro is intended to run
through the spreadsheet and set up all the formulas at the beginning.

Thanks

Thanks




"jk22" wrote:

Thanks guys - yes my example did have a circular reference - good point.

I have tried the suggestions but cannot make it work.

However I would like to formula to show up in excel spreadsheet as it does
using absolute referencing e.g. Cells(k + l, 20).Formula = "=A1/B2"

But using the relative referencing with cells notation to determine the
formula.

Any other suggestions appreciated.




"jk22" wrote:

Hi - I am trying to write the following in a macro in excel

For k = 8 to 1000
.......
For l = 1 To 50
If Cells(k + l, 2) < "" Then
Cells(k + l, 19).Formula = "=cells(k+l,19)/cells(k,19)" - does not
work in xls
Cells(k + l, 20).Formula = "=A1/B2" -
works in xls
End If
Next
.........

Can anyone suggest how to do this in loops?