using variables in named ranges vba

[novel24]

Hi. The following code allows me to name a range, but I must manually choose the column # and the names for each range each time. I would like to find a way to automate this process so I can just input at the top the column number, and the name for each named range I desire.

Sub namedrange()
'
' namedrange Macro
'
'Creates a named range for specified time-series data. Note: data must
'be organized with dates in 'left-hand' column; values in right.

'I want the column number in 'Add Date' to be variable, and the name creations to
'variable.

'Add Dates to named range

ActiveWorkbook.Names.Add NAME:="Dates", RefersToR1C1:= _
"=OFFSET(Sheet1!R4C7,,,COUNTA(Sheet1!R4C7:R500C7),1)"
ActiveWorkbook.Names("Dates").Comment = ""


'Add Values to named range

ActiveWorkbook.Names.Add NAME:="Values", RefersToR1C1:= _
"=OFFSET(Dates,0,1)"
ActiveWorkbook.Names("Values").Comment = ""



End Sub

 

[Andrew Poulsom]

Where do you want to put the column number and name? On a worksheet somewhere?

 

[xld]

Public Sub NameRange(col As Long, RangeNAme As String)

ActiveWorkbook.Names.Add NAME:=RangeName, RefersToR1C1:= _
"=OFFSET(Sheet1!R4C" & col & ",,,COUNTA(Sheet1!R4C" & col & ":R500C" & col & "),1)"
End Sub

 

[novel24]

Public Sub NameRange(col As Long, RangeNAme As String)

ActiveWorkbook.Names.Add NAME:=RangeName, RefersToR1C1:= _
"=OFFSET(Sheet1!R4C" & col & ",,,COUNTA(Sheet1!R4C" & col & ":R500C" & col & "),1)"
End Sub

This seems more like it -- how would I change the range name in this case (since it is linked through my code), thanks for the column part though.

 

[novel24]

Not sure yet. I probably would eventually create a message box of sorts that would ask for a name input and the column the data is located, but right now I'd rather just automate the process. I'm just looking to modify less code, so something that I only have to enter at the top of the code is fine.

Where do you want to put the column number and name? On a worksheet somewhere?

 

[xld]

This seems more like it -- how would I change the range name in this case (since it is linked through my code), thanks for the column part though.

You pass it as one of the procedure call parameters.