entering formula using cells notation
 

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?

entering formula using cells notation
 

Cells(k + l, 19).Formula = "=cells(" & k+l & ",19)/cells(" & k & ",19)"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"jk22" wrote in message
...
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?

entering formula using cells notation
 

What you are looking for is probably FormulaR1C1 style referencing (e.g.
Cells(8, 19).FormulaR1C1 ="RC[1]/RC[2]" results in =T8/U8, but

Cells(k + l, 19).Formula = "=cells(k+l,19)/cells(k,19)"

would be a circular reference: Cells(k + l, 19) cannot reference to itself,

so specify a correct formula!

Regards,
Stefi

€žjk22€� ezt Ã*rta:

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?

entering formula using cells notation
 

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?

entering formula using cells notation
 

cells(k+l,19).formula="=R1C1/R2C2"

results in formula with absolute referencing =$A$1/$B$2 in cell (k+l,19).

Is this what you want?

Regards,
Stefi

€žStefi€� ezt Ã*rta:

What you are looking for is probably FormulaR1C1 style referencing (e.g.
Cells(8, 19).FormulaR1C1 ="RC[1]/RC[2]" results in =T8/U8, but

Cells(k + l, 19).Formula = "=cells(k+l,19)/cells(k,19)"

would be a circular reference: Cells(k + l, 19) cannot reference to itself,

so specify a correct formula!

Regards,
Stefi

€žjk22€� ezt Ã*rta:

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?

entering formula using cells notation
 

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?

entering formula using cells notation
 

If you don't tell us the rules of determining cell references, I can give you
only general hints:

R1C1 style creates absolute references: R1 means row1, C1 means column1 that
is column A, so R1C1 means cell A1.
R[1]C[1] style creates relative references: R[1] means row 1 row down from
the hosting cell, C[1] means column 1 to the right from the hosting cell,
that is - if the hosting cell is A1 - R[1]C[1] means cell B2.

If you want to use variables, like k and l in your example, instead of
constant values between [] brackets, you have to use the following syntax:

ActiveCell.FormulaR1C1 ="R[" & k & "]C[" & l & "]"

means cell k row down, l column to the right from the active (hosting) cell.


Regards,
Stefi

€žjk22€� ezt Ã*rta:

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?