Defining an R1C1 style dynamic named range using vba

[Mr.Frog.to.you]

Hi everyone,

I have hit a small snag in my programming that is driving me nuts. I
am sure it is just a syntax issue but I cant seem to figure it out.

I have a worksheet, and on this worksheet are various columns of data.
The first row in every column has the name that is to be used for
making a named range, and the data is placed directly below in the
same column (row 2 onwards). No ranges are wider than 1 column, but
each range can have varying lengths (numbers of rows).

I have code that works through the first entire first row, and for
each value that it finds it uses code to define the named range for
that column. The name definition doesnt work for me in R1C1 style, and
I dont know how to convert the value to A1 style, so I try too work
with R1C1 because the end user will never see it anyway...

What I have as code is as follows:

Sub GenerateNamedRanges()
Dim S As Worksheet
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Dim Referral As String
Application.Volatile

Set S = ThisWorkbook.Worksheets("Sheet1")

Set WorkRange = S.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)

CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
Referral = "=" & S.Name & "!R1C1,"
Referral = Referral & WorkRange(i).Column & "," &
WorkRange(i).Row +1
Referral = Referral & ":INDEX(" & S.Name & "!R1C1,"
Referral = Referral & WorkRange(i).Column & ":" &
WorkRange(i).Column
Referral = Referral & ",COUNTA(" & S.Name & "!R1C1,"
Referral = Referral & WorkRange(i).Column & ":" &
WorkRange(i).Column & "))"
ThisWorkbook.Names.Add Name:=WorkRange(i).Value,
RefersToR1C1:=Referral
End If
Next i
End Sub

I have tried to build the necessary formula for the dynamic named
range using the R1C1 notation but I just cant get it to work. I need
to get the thing to set the range for the located column from row two
to the end of the data in that row (row 1 is the name of the range and
I cant have it mixed in with the values themselves).

If anyone can point me in the right direction then please let me know
how to go about this. I appreciate any suggestions.

Cheers

The Frog

 

[Joel]

I got the errors removed. A number of problems. You had 1:1 which need to
be range(r1c3). also before index you had a semicolon instead of a comma.
just compare the differences.


For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
Referral = "=" & s.Name & "!R1C1,"
Referral = Referral & "Range(R" & WorkRange(i).Column _
& "C" & WorkRange(i).Row + 1 & ")"
Referral = Referral & ",INDEX(" & s.Name & "!R1C1,"
Referral = Referral & "range(R" & WorkRange(i).Column & _
"C" & WorkRange(i).Column & ")"
Referral = Referral & ",COUNTA(" & s.Name & "!R1C1,"
Referral = Referral & "range(R" & WorkRange(i).Column & _
"C" & WorkRange(i).Column & ")))"
ThisWorkbook.Names.Add Name:=WorkRange(i).Value, _
RefersToR1C1:=Referral

 

[Mr.Frog.to.you]

Hi Joel,

Thankyou so much, that worked perfectly. Thankyou, thankyou,
THANKYOU

Cheers

The Frog

 

[Mr.Frog.to.you]

Hi again,

Slight update - the code didnt work perfectly, it just appeared to
work perfectly. What is was doing was placing a formula in the named
ranges list, but it actually didnt refer to anything.

Anyway, here is the code that works:

Sub GenerateNamedRanges(TargetSheet As String)
Dim S As Worksheet
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Dim Referral As String
Application.Volatile

Set S = ThisWorkbook.Worksheets(TargetSheet)
S.Activate

Set WorkRange = S.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)

CellCount = WorkRange.Count

For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
Referral = "=" & S.Name & "!R" & WorkRange(i).Row + 1 &
"C" & WorkRange(i).Column & ":"

Referral = Referral & "INDEX(" & S.Name & "!C" &
WorkRange(i).Column & ":" & "C" & WorkRange(i).Column & ","

Referral = Referral & "COUNTA(" & S.Name & "!C" &
WorkRange(i).Column & ":" & "C" & WorkRange(i).Column & "))"

ThisWorkbook.Names.Add Name:=WorkRange(i).Value,
RefersToR1C1:=Referral, Visible:=True
End If
Next i
End Sub

This will look at the first row of a worksheet, treat each cell with a
value in it as a range to be defined and use the cell value as the
name, and then pump the formula into the named range list for each
column. The named ranges are dynamic.

So in short - each column with a list of values starting in the
topmost cell will have a dynamic named range defined, the name of the
range will be whatever value is in the topmost cell, and the range is
dynamic starting from the second cell in the column and reaching down
to the last used cell.

Cheers

The Frog