Using a variable as a Row number

[Phil Hennessy]

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

 

[Harald Staff]

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

 

[Brian at Amphenol]

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

 

[Tom Ogilvy]

if you want to move Click_ThankYou down on row

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