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