How to convert relative formula to locked for pasting purposes?

  • Thread starter Thread starter Norm
  • Start date Start date
N

Norm

Hi,

Hopefully someone can help me. Win2000Pro & Excel2002

Here's what I have...

ActiveCell.FormulaR1C1 =
"=IF(COUNTIF(RC[-2]:R[1001]C[-2],RC[-1])>=1,1,"""")"
but as it gets filled down the rows increment as well, so

here's what I need (first time)

ActiveCell.FormulaR1C1 =
"=IF(COUNTIF(R11C64:R1010C64,RC[-1])>=1,1,"""")"

but the next time I would need

ActiveCell.FormulaR1C1 =
"=IF(COUNTIF(R11C65:R1010C65,RC[-1])>=1,1,"""")"

because I then fill it to a 1000 cells in the column

ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A1000"),
Type:=xlFillDefault

This is great the first time I run the macro, but the next time, since
the range is fixed, it's wrong because I move over one column each
time. Is there a way to keep my relative reference but paste it so it
doesn't increment the cells throughout the fill? My data will always
be in the same 1000 rows, but the columns increment daily.

Thanks!!!

Norm
 
Based on later posts, it appears Norm remembered how to make absolute and
relative referencing mixed.
 
Based on later posts, it appears Norm remembered how to make absolute and
relative referencing mixed.
Click to expand...

It twas a verrry bad week.

Norm


Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good
 
Back
Top