Bob Phillips
12/14/2006 10:21:00 PM
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" <Antonio@discussions.microsoft.com> wrote in message
news:A6FE73AD-5C6F-410F-9488-4218E10333A1@microsoft.com...
> 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" <Antonio@discussions.microsoft.com> wrote in message
>> news:AD10A5DF-6A9D-4004-B018-371D32D1128D@microsoft.com...
>> >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()
>>
>>
>>