Anyone know why this won't work? Can I not mix R1c1 with a direct cell
reference like B1? And if not, How do I replace $B$3 with something in r1c1
format? Thanks!


Set colb = ActiveCell.Offset(0, -2)
colb.FormulaR1C1 = "=IF(RC[-1]=""Open"",MAX($B$3:R[-1]C[-2])+1,"""")"

no, you can't. Use:

Set colb = ActiveCell.Offset(0, -2)
colb.FormulaR1C1 = "=IF(RC[-1]=""Open"",MAX(R3C2:R[-1]C[-2])+1,"""")"


--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Anyone know why this won't work? Can I not mix R1c1 with a direct cell
reference like B1? And if not, How do I replace $B$3 with something in
r1c1
format? Thanks!


Set colb = ActiveCell.Offset(0, -2)
colb.FormulaR1C1 = "=IF(RC[-1]=""Open"",MAX($B$3:R[-1]C[-2])+1,"""")"

Yes - you cannot mix...

R1C1 reference is straight forward

R1 refers to row 1, change the number to whatever row you want
C1 refers to column A, B=2, C = 3 ..... (numbers are absolute reference)

So $B$3 = R3C2

When you put the number in brakets [1] it becomes a relative reference to
the cell it is in.
1 is one cell away, 2 is two .... Add a - and the direction is reversed.
Works on row and column

so R[1]C[1] is one cell down and one cell to the right
R[-1]C[-1] is one cell up and one cell to the left.

removing the numbers references this row and/or this column
RC is this cell
--
steveB

Remove "AYN" from email to respond
"Steph" wrote in message
...
Anyone know why this won't work? Can I not mix R1c1 with a direct cell
reference like B1? And if not, How do I replace $B$3 with something in
r1c1
format? Thanks!


Set colb = ActiveCell.Offset(0, -2)
colb.FormulaR1C1 = "=IF(RC[-1]=""Open"",MAX($B$3:R[-1]C[-2])+1,"""")"