entering formula using cells notation

[jk22]

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?

 

[Niek Otten]

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

 

[Stefi]

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:

 

[jk22]

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.

 

[Stefi]

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?

 

[jk22]

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

 

[Stefi]

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: