Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum over a variable number of cells | Excel Discussion (Misc queries) | |||
Variable row number in Sum($A$1:A10) formula | Excel Discussion (Misc queries) | |||
How to sum a variable number of items | Excel Worksheet Functions | |||
reference with variable row number ? | Excel Worksheet Functions | |||
deleting a variable number of columns | Excel Programming |