Using a variable as a Row number
 

I'm using named cells as a reference in a worksheet.

This is the code that takes me to a cell, and loads that row as a
variable -
in this case 79

Dim r1 As Integer

Application.Goto Reference:="Click_ThankYou"

r1 = ActiveCell.Row

This is the code that the macro generator produces when I do a Insert
-
Names - Define and move the cursor down one cell.

ActiveWorkbook.Names.Add Name:="Click_ThankYou",
RefersToR1C1:= _
"='Clickthrough Detail'!R80C1"

What I'm trying to do is replace the R80C1 with R&r1+1&C1, but i'm
having trouble with the formatting. Does anyone know what the
formatting looks like to replace the 80 with the variable r1+1?

Best Regards,

Phil Hennessy

Using a variable as a Row number
 

Hi Phil

Without error trapping:

Sub test()
Dim L As Long
L = Val(InputBox("Row:"))
ActiveWorkbook.Names.Add _
Name:="Click_ThankYou", _
RefersToR1C1:= _
"='Clickthrough Detail'!R" & L & "C1"
End Sub

HTH. Best wishes Harald

"Phil Hennessy" skrev i melding
om...
I'm using named cells as a reference in a worksheet.

This is the code that takes me to a cell, and loads that row as a
variable -
in this case 79

Dim r1 As Integer

Application.Goto Reference:="Click_ThankYou"

r1 = ActiveCell.Row

This is the code that the macro generator produces when I do a Insert
-
Names - Define and move the cursor down one cell.

ActiveWorkbook.Names.Add Name:="Click_ThankYou",
RefersToR1C1:= _
"='Clickthrough Detail'!R80C1"

What I'm trying to do is replace the R80C1 with R&r1+1&C1, but i'm
having trouble with the formatting. Does anyone know what the
formatting looks like to replace the 80 with the variable r1+1?

Best Regards,

Phil Hennessy

Using a variable as a Row number
 

To reset the named range, I would do something like this:

NewRng = "='Clickthrough Detail'!R" & r1 + 1 & "C1"
ActiveWorkbook.Names.Add Name:="Click_ThankYou",
RefersToR1C1:= NewRng

-----Original Message-----
I'm using named cells as a reference in a worksheet.

This is the code that takes me to a cell, and loads that
row as a
variable -
in this case 79

Dim r1 As Integer

Application.Goto Reference:="Click_ThankYou"

r1 = ActiveCell.Row

This is the code that the macro generator produces when I
do a Insert
-
Names - Define and move the cursor down one cell.

ActiveWorkbook.Names.Add Name:="Click_ThankYou",
RefersToR1C1:= _
"='Clickthrough Detail'!R80C1"

What I'm trying to do is replace the R80C1 with
R&r1+1&C1, but i'm
having trouble with the formatting. Does anyone know
what the
formatting looks like to replace the 80 with the variable
r1+1?

Best Regards,

Phil Hennessy
.

Using a variable as a Row number
 

if you want to move Click_ThankYou down on row

Range("Click_ThankYou").Offset(1,0).Name = "Click_ThankYou"

--
Regards,
Tom Ogilvy

"Phil Hennessy" wrote in message
om...
I'm using named cells as a reference in a worksheet.

This is the code that takes me to a cell, and loads that row as a
variable -
in this case 79

Dim r1 As Integer

Application.Goto Reference:="Click_ThankYou"

r1 = ActiveCell.Row

This is the code that the macro generator produces when I do a Insert
-
Names - Define and move the cursor down one cell.

ActiveWorkbook.Names.Add Name:="Click_ThankYou",
RefersToR1C1:= _
"='Clickthrough Detail'!R80C1"

What I'm trying to do is replace the R80C1 with R&r1+1&C1, but i'm
having trouble with the formatting. Does anyone know what the
formatting looks like to replace the 80 with the variable r1+1?

Best Regards,

Phil Hennessy