You can do it in one operation, like so

Range("M1:N25").Formula="=IF($Q198=R$37,$N198,0)"

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Antonio" wrote in message
...
I need to insert programmatically the following formula in several adjacent
cells

=IF($Q198=R$37,$N198,0)

198 is the row number that varies
R is the column letter that varies

The rest is constant

I cannot copy the cells from above because sometimes I am not supposed to.
Copying the cells from above manually does work.

I am trying:

.Cells(r, 18).Formula = "=IF($Q" & r & "=" & Column() & "$37" & ",$N" & r
&
",0)"

where r is the row number
I need to figure out the column()

You can also use R1C1 notation

.Range(Cells(ct, 18), .Cells(ct, 23)).FormulaR1C1 =
"=IF(RC17=R37C,RC14,0)"


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Antonio" wrote in message
...
Hi Bob,

Your solution looks powerful,

I have

.Cells(ct, 18).FormulaR1C1 = "=IF(RC17=R37C,RC14,0)"
.Cells(ct, 19).FormulaR1C1 = "=IF(RC17=R37C,RC14,0)"
.Cells(ct, 20).FormulaR1C1 = "=IF(RC17=R37C,RC14,0)"
.Cells(ct, 21).FormulaR1C1 = "=IF(RC17=R37C,RC14,0)"
.Cells(ct, 22).FormulaR1C1 = "=IF(RC17=R37C,RC14,0)"
.Cells(ct, 23).FormulaR1C1 = "=IF(RC17=R37C,RC14,0)"

How do I use your way?

.Range(Cells(ct, 18), Cells(ct, 23)).Formula = "=IF($Q198=R$37,$N198,0)"

does not work, obviously.



"Bob Phillips" wrote:

You can do it in one operation, like so

Range("M1:N25").Formula="=IF($Q198=R$37,$N198,0)"

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Antonio" wrote in message
...
I need to insert programmatically the following formula in several
adjacent
cells

=IF($Q198=R$37,$N198,0)

198 is the row number that varies
R is the column letter that varies

The rest is constant

I cannot copy the cells from above because sometimes I am not supposed
to.
Copying the cells from above manually does work.

I am trying:

.Cells(r, 18).Formula = "=IF($Q" & r & "=" & Column() & "$37" & ",$N" &
r
&
",0)"

where r is the row number
I need to figure out the column()