Absolute Reference to current row 1

[WaterSprite]

[MyRow3 = Cells(Application.Rows.Count, 3).End(xlUp).Row + 8]
[Cells(MyRow3, 3).Activate]

[ActiveWorkbook.Names.Add Name:=("PB9"), _
RefersToR1C1:="=!RC3:RC16"]

[Range("PB9").FormulaR1C1 = "=RANDBETWEEN(1,59)"]
[Range("PB9").Interior.ColorIndex = 4]

I thought in R1C1 notation that R was an absolute reference to the current row.

No matter what I do, Range("PB_9") ends up at Row1, Col 3,
unless I reference Row9 in the RC3:RC16 portion of the add
name. And the RandBetween and interior.colorindex are not important, I just use them as indicators.

 

[PHV]

Replace this:
RefersToR1C1:="=!RC3:RC16"
with this:
RefersToR1C1:="=" & ActiveSheet.Name & "!R" & MyRow3 & "C3:R" & MyRow3 & "C16"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886